use ORM istead of SQL On Fri, Apr 12, 2019, 7:42 PM Michael Thomas <michael.thomas.s...@gmail.com> 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 <mob...@aldian.net> > 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, <michael.thomas.s...@gmail.com> >> 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 django-users+unsubscr...@googlegroups.com. >>> To post to this group, send email to django-users@googlegroups.com. >>> 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 django-users+unsubscr...@googlegroups.com. >> To post to this group, send email to django-users@googlegroups.com. >> 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 django-users+unsubscr...@googlegroups.com. > To post to this group, send email to django-users@googlegroups.com. > 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 django-users+unsubscr...@googlegroups.com. To post to this group, send email to django-users@googlegroups.com. 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.
