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
8439	Q objects still incorrectly choosing inner join over outer join	mikemintz	Malcolm Tredinnick	"I am basically reopening #3592 with an example that is broken in the latest SVN. I am using SVN r8445.



Here are my test models:

{{{
class Enemy(models.Model):
    pass

class Troop(models.Model):
    pass

class Soldier(models.Model):
    troop = models.ForeignKey(Troop)

class TroopKill(models.Model):
    enemy = models.ForeignKey(Enemy)
    troop = models.ForeignKey(Troop)

class SoldierKill(models.Model):
    enemy = models.ForeignKey(Enemy)
    soldier = models.ForeignKey(Soldier)
    grenade = models.BooleanField()
}}}

I want to find all enemies that were killed either by my soldier, or by the troop my soldier is in. So I execute the following query:

{{{
direct_kills = Q(soldierkill__soldier__pk=1)
troop_kills = Q(troopkill__troop__soldier__pk=1)
all_kills = direct_kills | troop_kills
print Enemy.objects.filter(all_kills).all()
}}}

And I get the correct SQL:

{{{
SELECT ""enemy"".""id"" FROM ""enemy""
LEFT OUTER JOIN ""soldierkill"" ON (""enemy"".""id"" = ""soldierkill"".""enemy_id"")
LEFT OUTER JOIN ""troopkill""   ON (""enemy"".""id"" = ""troopkill"".""enemy_id"")
LEFT OUTER JOIN ""troop""       ON (""troopkill"".""troop_id"" = ""troop"".""id"")
LEFT OUTER JOIN ""soldier"" T6  ON (""troop"".""id"" = T6.""troop_id"")
WHERE (""soldierkill"".""soldier_id"" = 1  OR T6.""id"" = 1 )
}}}

But when I try to add just one more condition (the direct kill had to have grenade=True):

{{{
direct_kills = Q(soldierkill__soldier__pk=1,soldierkill__grenade=True)
troop_kills = Q(troopkill__troop__soldier__pk=1)
all_kills = direct_kills | troop_kills
print Enemy.objects.filter(all_kills).all()
}}}

It switches incorrectly to inner joins.

{{{
SELECT ""enemy"".""id"" FROM ""enemy""
INNER JOIN ""soldierkill"" ON (""enemy"".""id"" = ""soldierkill"".""enemy_id"")
INNER JOIN ""troopkill""   ON (""enemy"".""id"" = ""troopkill"".""enemy_id"")
INNER JOIN ""troop""       ON (""troopkill"".""troop_id"" = ""troop"".""id"")
INNER JOIN ""soldier"" T6  ON (""troop"".""id"" = T6.""troop_id"")
WHERE ((""soldierkill"".""grenade"" = true  AND ""soldierkill"".""soldier_id"" = 1 ) OR T6.""id"" = 1 )
}}}

Am I doing something wrong, or is this broken?

Thanks,
Mike"		closed	Database layer (models, ORM)	dev		fixed	1.0-blocker		Accepted	0	0	0	0	0	0