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, jga...@gmail.com 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 django-developers+unsubscr...@googlegroups.com.
To post to this group, send email to django-developers@googlegroups.com.
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.
