I've only had a quick look at your problem, but looks like maybe this section of the Django documentation could be useful: https://docs.djangoproject.com/en/dev/topics/db/queries/#spanning-multi-valued-relationships
On Friday, 12 April 2019 15:10:59 UTC+2, Michael Thomas 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/8142abe8-1d14-4eff-aa6b-0f281ce14181%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.
