I have encountered a problem with joins in Django's ORM. I had assumed
that
queryset.filter(**a) | queryset.filter(**b)
is functionally equivalent to
queryset.filter(models.Q(**a) | models.Q(**b))
for all a and b. That is not what is happening. If my assumption is
not true, then what are the intended semantics for the first form? If
my assumption is correct, then this must be a bug in Django.
The code I'm using, and the generated SQL from ._as_sql() is below.
Tags are many-to-many with Projects, Projects are many-to-many with
Profiles in two separate maps: teammembers and investigators. Profiles
are also many-to-many with Tags in two separate maps, an include
(profiles_watching) and an exclude (profiles_ignoring) map. The query
is to find Tags linked via Projects to a Profile, plus those that are
directly included and not those directly excluded. 'self' is a
models.Manager for research.Tag.
The first form is the original version that omits some of the results
I'm expecting to see.
of_interest_automatic = (
self.filter(project__teammembers=profile) |
self.filter(project__investigators=profile)
).distinct()
return (
of_interest_automatic |
self.filter(profiles_watching=profile).distinct()
).exclude(profiles_ignoring=profile).distinct()
(of_interest_automatic is actually a separate method, which is why
distinct() is repeated)
('SELECT DISTINCT U0.`id` FROM `research_tag` U0 INNER JOIN
`research_project_tags` U1 ON (U0.`id` = U1.`tag_id`) INNER JOIN
`research_project` U2 ON (U1.`project_id` = U2.`id`) LEFT OUTER JOIN
`research_project_teammembers` U3 ON (U2.`id` = U3.`project_id`) LEFT
OUTER JOIN `research_project_investigators` U5 ON (U2.`id` =
U5.`project_id`) LEFT OUTER JOIN `people_profile_tags_include` U6 ON
(U0.`id` = U6.`tag_id`) WHERE ((U3.`profile_id` = %s OR
U5.`profile_id` = %s OR U6.`profile_id` = %s ) AND NOT (U0.`id` IN
(SELECT U1.`tag_id` FROM `people_profile_tags_exclude` U1 WHERE
U1.`profile_id` = %s )))', (287, 287, 287, 287))
This is how I have rewritten it
return self.filter(
Q(project__teammembers=profile) |
Q(project__investigators=profile) |
Q(profiles_watching=profile)
).exclude(profiles_ignoring=profile).distinct()
('SELECT DISTINCT U0.`id` FROM `research_tag` U0 LEFT OUTER JOIN
`research_project_tags` U1 ON (U0.`id` = U1.`tag_id`) LEFT OUTER JOIN
`research_project` U2 ON (U1.`project_id` = U2.`id`) LEFT OUTER JOIN
`research_project_teammembers` U3 ON (U2.`id` = U3.`project_id`) LEFT
OUTER JOIN `research_project_investigators` U5 ON (U2.`id` =
U5.`project_id`) LEFT OUTER JOIN `people_profile_tags_include` U7 ON
(U0.`id` = U7.`tag_id`) WHERE ((U3.`profile_id` = %s OR
U5.`profile_id` = %s OR U7.`profile_id` = %s ) AND NOT (U0.`id` IN
(SELECT U1.`tag_id` FROM `people_profile_tags_exclude` U1 WHERE
U1.`profile_id` = %s )))', (287, 287, 287, 287))
Dan
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to django-us...@googlegroups.com.
To unsubscribe from this group, send email to
django-users+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en.
