I managed to get the desired behavior by doing the following ugly query: q_obj = (Q(a__confirmation=True) & Q(a__state=1)) | (Q(a__state__gt=1) & Q(a__state__lt=1)) bees = B.objects.filter(q_obj)
This is obviously not an ideal solution but is working for me so far... On Monday, November 4, 2013 3:24:47 PM UTC-2, [email protected] wrote: > > Anssi, > > Thanks for helping. > I'm sorry to say that your answer went somewhat over my head, my > proficiency with SQL is lacking. > > What I understood from your explanation: > - A filter/exclude that traverses a 1:N relationship(such as foreign key) > should target the same row with all of its criteria(kwargs). > - Complex queries don't work correctly in exclude when using > relationships in 1.5.x > - Complex queries don't work correctly in exclude when using > relationships in 1.6.x > > Did I understand correctly? > > If that was the whole of the situation I would be ok, I can work around > this issue with multiple exclude statements, such as: > bees = B.objects.exclude(a__confirmation=False, a__state=1) > bees = bees.exclude(a__confirmation__isnull=True, a__state=1) > That should be equivalent to what I was trying to do with: > confirm_q = Q(a__confirmation=False) | Q(a__confirmation__isnull=True) > bees = B.objects.exclude(confirm_q, a__state=1) > > But my solution of splitting the Q into two queries didn't work, for > either 1.5.5 or 1.6rc1. > Did I miss something? > > Gastal > > On Monday, November 4, 2013 2:43:55 PM UTC-2, Anssi Kääriäinen wrote: >> >> On Monday, November 4, 2013 6:06:55 PM UTC+2, Anssi Kääriäinen wrote: >>> >>> I'll look into this. >>> >> >> The situation is that this query didn't work properly in 1.5.x, but this >> doesn't work properly in 1.6.x either. >> >> The basic problem here is that in 1.5.x .exclude(Q(anything)) didn't work >> correctly. Using the example models, try these equivalent queries: >> bees1 = B.objects.exclude(Q(a__state=1)) >> bees2 = B.objects.exclude(a__state=1) >> >> bees1 produces querystr: >> SELECT "new_basic_b"."id" FROM "new_basic_b" INNER JOIN "new_basic_a" >> ON ("new_basic_b"."id" = "new_basic_a"."b_id") WHERE NOT >> ("new_basic_a"."state" = 1 ) >> >> while bees2 produces: >> SELECT "new_basic_b"."id" FROM "new_basic_b" WHERE NOT >> (("new_basic_b"."id" IN (SELECT U1."b_id" FROM "new_basic_a" U1 WHERE >> (U1."state" = 1 AND U1."b_id" IS NOT NULL)) AND "new_basic_b"."id" IS NOT >> NULL)) >> >> Note that bees2 has correctly subquery in it. The bees1 query will >> produce incorrect results. >> >> Now, for the example query, the same "exclude hiding" happens for >> Q(a__confirmation=False) | Q(a__confirmation__isnull=True). Due to this the >> produced query is: >> SELECT "new_basic_b"."id" FROM "new_basic_b" LEFT OUTER JOIN >> "new_basic_a" ON ("new_basic_b"."id" = "new_basic_a"."b_id") WHERE NOT >> (("new_basic_a"."confirmation" = False OR "new_basic_a"."confirmation" IS >> NULL) AND ("new_basic_b"."id" IN (SELECT U1."b_id" FROM "new_basic_a" U1 >> WHERE (U1."state" = 1 AND U1."b_id" IS NOT NULL)) AND "new_basic_b"."id" >> IS NOT NULL)) >> >> Note the LEFT OUTER JOIN for the ORed condition, but subquery for the >> state condition. That is incorrect, both filters should be in the same >> subquery. >> >> In 1.6.x the situation is, well, different. The generated query is: >> SELECT "new_basic_b"."id" FROM "new_basic_b" WHERE NOT >> (("new_basic_b"."id" IN (SELECT U1."b_id" FROM "new_basic_a" U1 WHERE >> U1."confirmation" = False ) OR "new_basic_b"."id" IN (SELECT U0."id" FROM >> "new_basic_b" U0 LEFT OUTER JOIN "new_basic_a" U1 ON ( U0."id" = U1."b_id" >> ) WHERE U1."confirmation" IS NULL)) AND "new_basic_b"."id" IN (SELECT >> U1."b_id" FROM "new_basic_a" U1 WHERE U1."state" = 1 )) >> >> Now we have each of the conditions correctly in a subquery, but in >> different subqueries which isn't correct (filters inside single >> .filter()/.exclude() should target the same row when having multiple >> clauses for the same multivalued relation). >> >> Complex filters in .exclude() didn't work correctly, and do not work >> correctly in the upcoming 1.6 either. If the results were correct in 1.5 >> that was luck, not a result of Django generating the correct query. It >> should be possible to construct data that highlights the problem in the 1.5 >> version of the query. >> >> - Anssi >> > -- You received this message because you are subscribed to the Google Groups "Django developers" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/django-developers. To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/004e52f5-85e5-4dd2-80ae-57683fa577af%40googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
