Re: Filtering OR-combined queries

Hi,

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

 

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.

--
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/CAHAc2jdWB-9FLJWZbcjuEp53-M9jGrv7YUN_hecWa4w4Nzkh%2BQ%40mail.gmail.com.