Naimur: I am - that SQL was in response to Aldian's question :) On Fri, Apr 12, 2019 at 8:23 PM naimur rahman <[email protected]> wrote:
> 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 > <https://groups.google.com/d/msgid/django-users/CAENN6BLoBbu8v_BTDaPYyaQ1AJzQGi2E9t1trg_9PwbzP-Rn3A%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/CAEdx1fqO1toFaB5qS6cduAJ5Hhmm6ybOjyCPvtQmy-RHBKWjGg%40mail.gmail.com. For more options, visit https://groups.google.com/d/optout.
