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