Filtering OR-combined queries

[Shaheed Haque]

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

[Sebastian Jung]

Take q for or Filterung:

https://stackoverflow.com/questions/6567831/how-to-perform-or-condition-in-django-queryset

--
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 django-users...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CAHAc2jcLVcFFiXRZtX_iGTJ2YJ0CNsObRFDyw1Ss6i3nV-X-vw%40mail.gmail.com.

[Shaheed Haque]

Hi,

On Fri, 30 Apr 2021 at 11:52, Sebastian Jung <sebasti...@gmail.com> 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

 

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CAKGT9mwaiSjyrrMJfrvoXMShfnqjxhonkHTbyOVdCGqJwqK85w%40mail.gmail.com.

[Simon Charette]

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

[Shaheed Haque]

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

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/a1c05a87-8521-4373-8fcc-de0e777b98b4n%40googlegroups.com.

[Shaheed Haque]

After testing with 3.2.1, I filed https://code.djangoproject.com/ticket/32717.

[Shaheed Haque]

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