Just to close the loop on this, the defect in
https://code.djangoproject.com/ticket/32717 is on its way to being
resolved, but my original reason for using OR'd queries rather than Q()
expressions was that I had not quite grokked the use of ~Q() to implement
.exclude(). Simon kindly pointed out the code could have been written like
this:
def jurisdiction_qs(for_jurisdiction):
filter_ = Q(jurisdiction=for_jurisdiction)
if for_jurisdiction != 'Universal':
filter_ |= Q(jurisdiction='Universal') & ~Q(
name__in=Buss.objects.filter(filter_).values_list('name', flat=True)
)
if for_jurisdiction != 'Company':
filter_ |= Q(jurisdiction='Company') & ~Q(
name__in=Buss.objects.filter(filter_).values_list('name', flat=True)
)
return Buss.objects.filter(filter_)
Notice the use of "& ~Q()"...
Thanks, Shaheed
On Wed, 5 May 2021 at 17:01, Shaheed Haque <[email protected]> wrote:
> After testing with 3.2.1, I filed
> https://code.djangoproject.com/ticket/32717.
>
> On Tue, 4 May 2021 at 13:23, Shaheed Haque <[email protected]> wrote:
>
>> Simon,
>>
>> Thanks for the heads up. At first glance, the release notes don't *quite*
>> seem to match what I see but as you suggest, I will certainly check the new
>> release before filing an issue.
>>
>> TTFN, Shaheed
>>
>> On Mon, 3 May 2021 at 16:10, Simon Charette <[email protected]> wrote:
>>
>>> Hello Shaheed,
>>>
>>> I didn't look at your issue into details but since it involves exclude,
>>> subqueries, an queryset combination and only manifests itself on Django 3.2
>>> I wouldn't be surprised if it was related to some regressions in this area
>>> that are fixed in Django 3.2.1 which is meant to be released tomorrow
>>>
>>> https://docs.djangoproject.com/en/3.2/releases/3.2.1/
>>>
>>> Cheers,
>>> Simon
>>> Le vendredi 30 avril 2021 à 09:58:53 UTC-4, [email protected] a
>>> écrit :
>>>
>>>> Hi,
>>>>
>>>> On Fri, 30 Apr 2021 at 11:52, Sebastian Jung <[email protected]>
>>>> wrote:
>>>>
>>>>> Take q for or Filterung:
>>>>>
>>>>>
>>>>> https://stackoverflow.com/questions/6567831/how-to-perform-or-condition-in-django-queryset
>>>>>
>>>>
>>>> Thanks for the response, but the OR is not the problem: it works just
>>>> fine. (FWIW, I used the "|" form rather than the Q() form because it is not
>>>> clear to me how one can express a .exclude() when using Q()). The problem
>>>> here is that the result of the OR cannot be reliably .filter()d.
>>>>
>>>> As I tried to explain, I can see that in some cases, a .filter() of the
>>>> OR'd result does indeed add extra SQL to the query, whereas in others it
>>>> does not:
>>>>
>>>> qs_or_result.filter(a='b')
>>>>
>>>> This works for some values of "a" but not others, I can use this to
>>>> achieve the same effect:
>>>>
>>>> Buss.objects.filter(a='b').intersection(qs_or_result)
>>>>
>>>> though it is rather clumsy!!! FWIW, this is with Django 3.2. I'm
>>>> inclined to think this is a bug in the ORM, though I have not dived into
>>>> the code to track it down.
>>>>
>>>> Thanks, Shaheed
>>>>
>>>>
>>>>>
>>>>>
>>>>> Shaheed Haque <[email protected]> schrieb am Fr., 30. Apr. 2021,
>>>>> 02:43:
>>>>>
>>>>>> Hi,
>>>>>>
>>>>>> I have a query which ORs some selects on a single table together like
>>>>>> this:
>>>>>>
>>>>>> jurisdiction = 'aaa'
>>>>>> qs = Buss.objects.filter(jurisdiction=jurisdiction)
>>>>>> qs = qs | Buss.objects.filter(jurisdiction='xxx').exclude(name__in
>>>>>> =qs.values_list('name', flat=True))
>>>>>> qs = qs | Buss.objects.filter(jurisdiction='yyy').exclude(name__in
>>>>>> =qs.values_list('name', flat=True))
>>>>>>
>>>>>> This seems to work just fine (and the raw SQL looks suitably
>>>>>> complicated):
>>>>>>
>>>>>> SELECT "paiyroll_buss"."id", "paiyroll_buss"."jurisdiction",
>>>>>> "paiyroll_buss"."name", "paiyroll_buss"."description" FROM
>>>>>> "paiyroll_buss"
>>>>>> WHERE ("paiyroll_buss"."jurisdiction" = aaa OR
>>>>>> ("paiyroll_buss"."jurisdiction" = xxx AND NOT ("paiyroll_buss"."name" IN
>>>>>> (SELECT U0."name" FROM "paiyroll_buss" U0 WHERE U0."jurisdiction" =
>>>>>> aaa)))
>>>>>> OR ("paiyroll_buss"."jurisdiction" = yyy AND NOT ("paiyroll_buss"."name"
>>>>>> IN
>>>>>> (SELECT U0."name" FROM "paiyroll_buss" U0 WHERE (U0."jurisdiction" = aaa
>>>>>> OR
>>>>>> (U0."jurisdiction" = xxx AND NOT (U0."name" IN (SELECT U0."name" FROM
>>>>>> "paiyroll_buss" U0 WHERE U0."jurisdiction" = aaa))))))))
>>>>>>
>>>>>> Now, if I post-filter qs using something other than "aaa" (the first
>>>>>> term above) like this:
>>>>>>
>>>>>> qs.filter('xxx)
>>>>>>
>>>>>> then the resulting SQL has an 'AND "paiyroll_buss"."jurisdiction" =
>>>>>> xxx' as one might expect. However, if I try to post-filter qs like this:
>>>>>>
>>>>>> qs.filter('aaa') # 'aaa' was the first term in the original trio
>>>>>> of clauses
>>>>>>
>>>>>> Then the formed SQL looks like this:
>>>>>>
>>>>>> SELECT "paiyroll_buss"."id", "paiyroll_buss"."jurisdiction",
>>>>>> "paiyroll_buss"."name", "paiyroll_buss"."description" FROM
>>>>>> "paiyroll_buss"
>>>>>> WHERE ("paiyroll_buss"."jurisdiction" = aaa OR
>>>>>> ("paiyroll_buss"."jurisdiction" = xxx AND NOT ("paiyroll_buss"."name" IN
>>>>>> (SELECT U0."name" FROM "paiyroll_buss" U0 WHERE U0."jurisdiction" =
>>>>>> aaa)))
>>>>>> OR ("paiyroll_buss"."jurisdiction" = yyy AND NOT ("paiyroll_buss"."name"
>>>>>> IN
>>>>>> (SELECT U0."name" FROM "paiyroll_buss" U0 WHERE (U0."jurisdiction" = aaa
>>>>>> OR
>>>>>> (U0."jurisdiction" = xxx AND NOT (U0."name" IN (SELECT U0."name" FROM
>>>>>> "paiyroll_buss" U0 WHERE U0."jurisdiction" = aaa))))))))
>>>>>>
>>>>>> i.e. just like the original 3-clause query. (I realise that in this
>>>>>> case, the query degenerates to the first term). What am I missing?
>>>>>>
>>>>>> Any clues appreciated.
>>>>>>
>>>>>> Thanks, Shaheed
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>> --
>>>>>> You received this message because you are subscribed to the Google
>>>>>> Groups "Django users" group.
>>>>>> To unsubscribe from this group and stop receiving emails from it,
>>>>>> send an email to [email protected].
>>>>>> To view this discussion on the web visit
>>>>>> https://groups.google.com/d/msgid/django-users/CAHAc2jcLVcFFiXRZtX_iGTJ2YJ0CNsObRFDyw1Ss6i3nV-X-vw%40mail.gmail.com
>>>>>> <https://groups.google.com/d/msgid/django-users/CAHAc2jcLVcFFiXRZtX_iGTJ2YJ0CNsObRFDyw1Ss6i3nV-X-vw%40mail.gmail.com?utm_medium=email&utm_source=footer>
>>>>>> .
>>>>>>
>>>>> --
>>>>> You received this message because you are subscribed to the Google
>>>>> Groups "Django users" group.
>>>>> To unsubscribe from this group and stop receiving emails from it, send
>>>>> an email to [email protected].
>>>>>
>>>> To view this discussion on the web visit
>>>>> https://groups.google.com/d/msgid/django-users/CAKGT9mwaiSjyrrMJfrvoXMShfnqjxhonkHTbyOVdCGqJwqK85w%40mail.gmail.com
>>>>> <https://groups.google.com/d/msgid/django-users/CAKGT9mwaiSjyrrMJfrvoXMShfnqjxhonkHTbyOVdCGqJwqK85w%40mail.gmail.com?utm_medium=email&utm_source=footer>
>>>>> .
>>>>>
>>>> --
>>> You received this message because you are subscribed to the Google
>>> Groups "Django users" group.
>>> To unsubscribe from this group and stop receiving emails from it, send
>>> an email to [email protected].
>>> To view this discussion on the web visit
>>> https://groups.google.com/d/msgid/django-users/a1c05a87-8521-4373-8fcc-de0e777b98b4n%40googlegroups.com
>>> <https://groups.google.com/d/msgid/django-users/a1c05a87-8521-4373-8fcc-de0e777b98b4n%40googlegroups.com?utm_medium=email&utm_source=footer>
>>> .
>>>
>>
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-users/CAHAc2je-C8UvP886JOoOF%3DgKXqDtx5p94-5pxqr0T9yNPSsWjQ%40mail.gmail.com.
