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
6154	Q objects don't work properly when using spanned relationships	Thomas Steinacher <tom@…>	nobody	"{{{
>>> from wcenter.models import Website, Domain
>>> from django.db.models import Q
>>> websites = Website.objects.filter(pk__in=[182,183])
>>> d = Domain.objects.get(name='mydomain')
>>> websites
[<Website: 182>, <Website: 183]
}}}

Until now, that's fine. Now let's try the following:

{{{
>>> q = websites.filter(Q(domain=d) | Q(websitealias__domain=d))
>>> q
[<Website: 182>, <Website: 182>]
}}}

This is the bug. Why is website 183 gone? Here's the SQL (I am not an SQL expert, but maybe Django should use a LEFT JOIN instead of an INNER JOIN):

{{{
>>> def show_sql(q):
...     cols, sql, args = q._get_sql_clause()
...     return ""SELECT %s %s"" % (', '.join(cols), sql % tuple(args))
...
>>> show_sql(q)
'SELECT `wcenter_website`.`id`, `wcenter_website`.`domain_id`, [...]  FROM `wcenter_website` INNER JOIN `wcenter_websitealias` AS `wcenter_website__websitealias` ON `wcenter_website`.`id` = `wcenter_website__websitealias`.`website_id` WHERE (`wcenter_website`.`id` IN (182,183) AND (`wcenter_website`.`domain_id` = 2 OR `wcenter_website__websitealias`.`domain_id` = 2))'
>>> show_sql(websites)
'SELECT `wcenter_website`.`id`, `wcenter_website`.`domain_id`, [...]  FROM `wcenter_website` WHERE (`wcenter_website`.`id` IN (182,183))'
}}}

I think the second website is missing because it doesn't have any website aliases:

{{{
>>> websites[0].websitealias_set.all()
[<WebsiteAlias>, <WebsiteAlias>]
>>> websites[1].websitealias_set.all()
[]
}}}

It looks like a LEFT JOIN would solve the problem:

{{{
mysql> SELECT `wcenter_website`.`id`, `wcenter_website`.`domain_id` FROM `wcenter_website` LEFT JOIN `wcenter_websitealias` AS `wcenter_website__websitealias` ON `wcenter_website`.`id` = `wcenter_website__websitealias`.`website_id` WHERE (`wcenter_website`.`id` IN (182,183) AND (`wcenter_website`.`domain_id` = 2));
+-----+-----------+
| id  | domain_id |
+-----+-----------+
| 182 |         2 | 
| 182 |         2 | 
| 183 |         2 | 
+-----+-----------+
3 rows in set (0.00 sec)

mysql> SELECT `wcenter_website`.`id`, `wcenter_website`.`domain_id` FROM `wcenter_website` INNER JOIN `wcenter_websitealias` AS `wcenter_website__websitealias` ON `wcenter_website`.`id` = `wcenter_website__websitealias`.`website_id` WHERE (`wcenter_website`.`id` IN (182,183));
+-----+-----------+
| id  | domain_id |
+-----+-----------+
| 182 |         2 | 
| 182 |         2 | 
+-----+-----------+
2 rows in set (0.00 sec)
}}}"		closed	Database layer (models, ORM)	dev		fixed	qs-rf-fixed		Accepted	0	0	0	0	0	0