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. For more options, visit https://groups.google.com/d/optout.
