id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
7290	filter	Ales Zoulek <ales.zoulek@…>	nobody	"When using OR operation on two Q objects, witch are both filtering on the same table, it leaves out one condition in WHERE clause.

Given this model and the filter, django will query database for:

{{{
SELECT ""a_team"".""id"", ""a_team"".""name"" FROM ""a_team"" 
   LEFT OUTER JOIN ""a_match"" ON (""a_team"".""id"" = ""a_match"".""home_team_id"")
   LEFT OUTER JOIN ""a_match"" T4 ON (""a_team"".""id"" = T4.""guest_team_id"")
   WHERE ""a_match"".""round_id"" = 1;
}}}

But it should use:
{{{
SELECT ""a_team"".""id"", ""a_team"".""name"" FROM ""a_team"" 
   LEFT OUTER JOIN ""a_match"" ON (""a_team"".""id"" = ""a_match"".""home_team_id"")
   LEFT OUTER JOIN ""a_match"" T4 ON (""a_team"".""id"" = T4.""guest_team_id"")
   WHERE ""a_match"".""round_id"" = 1 OR T4.round_id = 1;
}}}

Mind the difference in the where clause. Even though T4 is just alias for 'a_match' table, and both conditions are comparing 'round_id' for the same value (1), it's important to use both.



{{{
#!python
from django.db import models

class Team(models.Model):
    name = models.CharField(max_length=100)

class Round(models.Model):
    name = models.CharField(max_length=100)

class Match(models.Model):
    home_team = models.ForeignKey(Team, related_name='home_team_set')
    guest_team = models.ForeignKey(Team, related_name='guest_team_set')
    round = models.ForeignKey(Round)

__test__ = {'bug': """"""
# Data init
>>> r = Round(name='round 1')
>>> r.save()
>>> t1 = Team(name='Cze')
>>> t1.save()
>>> t2 = Team(name='USA')
>>> t2.save()
>>> Match(home_team=t1, guest_team=t2, round=r).save()


# Get teams with matches in round 'r'
>>> result = Team.objects.filter(models.Q(home_team_set__round=r) | models.Q(guest_team_set__round=r))
>>> len(result)
2


""""""}

}}}"		closed	Database layer (models, ORM)	dev		duplicate	qsrf-cleanup	ondrej.kohout@…	Unreviewed	0	0	0	0	0	0