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 ,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 [, >> q = websites.filter(Q(domain=d) | Q(websitealias__domain=d)) >>> q [, ] }}} 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() [, ] >>> 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