Re: Filtering OR-combined queries

Take q for or Filterung:

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

Shaheed Haque <shaheedhaque@gmail.com> 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 django-users+unsubscribe@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.

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