use ORM istead of SQL On Fri, Apr 12, 2019, 7:42 PM Michael Thomas <[email protected]> wrote:
> SELECT > "app_foo"."id", > "app_foo"."name" > FROM "app_foo" > INNER JOIN "app_bar" ON ( > "app_foo"."id" = "app_bar"."foo_id" > ) > WHERE ( > NOT ( > "app_foo"."id" IN ( > SELECT > U1."foo_id" > FROM "app_bar" U1 > WHERE U1."attribute_1" = 1 > ) > ) > AND "app_bar"."attribute_2" = 2 > ) > > On Fri, Apr 12, 2019 at 6:59 PM Aldian Fazrihady <[email protected]> > wrote: > >> What's the result of >> >> print(Foo.objects.exclude(bar__attribute=1).filter( >> bar__attribute_2=2).query) >> >> On Fri, 12 Apr 2019, 20:10 Michael Thomas, <[email protected]> >> wrote: >> >>> Hello everyone, >>> >>> I've run into what I believe is a limitation of the ORM that other >>> people must be dealing with somehow, but I can't seem to figure out a >>> sensible solution. >>> >>> I think it's easiest to describe the problem with code. >>> >>> For the following models: >>> >>> class Foo(models.Model): >>> name = models.CharField(max_length=64) >>> >>> >>> class Bar(models.Model): >>> foo = models.ForeignKey(Foo, on_delete=models.CASCADE) >>> attribute_1 = models.IntegerField() >>> attribute_2 = models.IntegerField() >>> >>> I want to select all Foo() that have 1 or more bar with attribute_1 not >>> equal to 1, and attribute_2 equal to 2. >>> >>> Eg. SQL something like this: >>> >>> SELECT >>> "app_foo"."id", >>> "app_foo"."name" >>> FROM "app_foo" >>> INNER JOIN "app_bar" ON ( >>> "app_foo"."id" = "app_bar"."foo_id" >>> ) >>> WHERE ( >>> "app_bar"."attribute_1" <> 1 >>> AND "app_bar"."attribute_2" = 2 >>> ) >>> >>> However, here's what I end up with... >>> >>> >>> print(Foo.objects.exclude(bar__attribute_1=1).filter(bar__attribute_2=2).query) >>> SELECT >>> "app_foo"."id", >>> "app_foo"."name" >>> FROM "app_foo" >>> INNER JOIN "app_bar" ON ( >>> "app_foo"."id" = "app_bar"."foo_id" >>> ) >>> WHERE ( >>> NOT ( >>> "app_foo"."id" IN ( >>> SELECT >>> U1."foo_id" >>> FROM "app_bar" U1 >>> WHERE U1."attribute_1" = 1 >>> ) >>> ) >>> AND "app_bar"."attribute_2" = 2 >>> ) >>> >>> print(Foo.objects.filter(~Q(bar__attribute_1=1), >>> bar__attribute_2=2).query) >>> Exact same SQL output as above >>> >>> Interestingly enough, a simple query for attribute_1=1 and attribute_2=2 >>> works as expected, so it would be trivial to do this with a __ne operator >>> (if it existed), without any other changes to the ORM: >>> >>> print(Foo.objects.filter(bar__attribute_1=1, bar__attribute_2=2).query) >>> SELECT >>> "app_foo"."id", >>> "app_foo"."name" >>> FROM "app_foo" >>> INNER JOIN "app_bar" ON ( >>> "app_foo"."id" = "app_bar"."foo_id" >>> ) >>> WHERE ( >>> "app_bar"."attribute_1" = 1 >>> AND "app_bar"."attribute_2" = 2 >>> ) >>> >>> Am I missing something here? How are other people tackling this? >>> >>> Kind Regards, >>> Michael Thomas >>> >>> -- >>> 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 post to this group, send email to [email protected]. >>> Visit this group at https://groups.google.com/group/django-users. >>> To view this discussion on the web visit >>> https://groups.google.com/d/msgid/django-users/d852fc10-5f5a-43e8-8dab-c796404867a8%40googlegroups.com >>> <https://groups.google.com/d/msgid/django-users/d852fc10-5f5a-43e8-8dab-c796404867a8%40googlegroups.com?utm_medium=email&utm_source=footer> >>> . >>> For more options, visit https://groups.google.com/d/optout. >>> >> -- >> 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 post to this group, send email to [email protected]. >> Visit this group at https://groups.google.com/group/django-users. >> To view this discussion on the web visit >> https://groups.google.com/d/msgid/django-users/CAN7EoAaXupYqNoPcHOcZ3OoA_07N--D4EPoEfZvkSWL%2BeLbD6A%40mail.gmail.com >> <https://groups.google.com/d/msgid/django-users/CAN7EoAaXupYqNoPcHOcZ3OoA_07N--D4EPoEfZvkSWL%2BeLbD6A%40mail.gmail.com?utm_medium=email&utm_source=footer> >> . >> For more options, visit https://groups.google.com/d/optout. >> > -- > 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 post to this group, send email to [email protected]. > Visit this group at https://groups.google.com/group/django-users. > To view this discussion on the web visit > https://groups.google.com/d/msgid/django-users/CAEdx1fpYmYHXX14iu3Enwc18Ojkk3%3DBfzTv3mGWVdf5BzZVv7w%40mail.gmail.com > <https://groups.google.com/d/msgid/django-users/CAEdx1fpYmYHXX14iu3Enwc18Ojkk3%3DBfzTv3mGWVdf5BzZVv7w%40mail.gmail.com?utm_medium=email&utm_source=footer> > . > For more options, visit https://groups.google.com/d/optout. > -- 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 post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/django-users. To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CAENN6BLoBbu8v_BTDaPYyaQ1AJzQGi2E9t1trg_9PwbzP-Rn3A%40mail.gmail.com. For more options, visit https://groups.google.com/d/optout.
