Complex query on inner join - case for __ne?
27 views
Skip to first unread message
Michael Thomas
Apr 12, 2019, 9:10:59 AM4/12/19
to Django users
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
Aldian Fazrihady
Apr 12, 2019, 9:30:00 AM4/12/19
to django...@googlegroups.com
What's the result of
print(Foo.objects.exclude(bar__attribute=1).filter(bar__attribute_2=2).query)
--
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...@googlegroups.com.
To post to this group, send email to django...@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.
For more options, visit https://groups.google.com/d/optout.
Michael Thomas
Apr 12, 2019, 9:42:41 AM4/12/19
to django...@googlegroups.com
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
)
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CAN7EoAaXupYqNoPcHOcZ3OoA_07N--D4EPoEfZvkSWL%2BeLbD6A%40mail.gmail.com.
naimur rahman
Apr 12, 2019, 10:53:46 AM4/12/19
to django...@googlegroups.com
use ORM istead of SQL
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CAEdx1fpYmYHXX14iu3Enwc18Ojkk3%3DBfzTv3mGWVdf5BzZVv7w%40mail.gmail.com.
Michael Thomas
Apr 12, 2019, 10:58:30 AM4/12/19
to django...@googlegroups.com
Naimur: I am - that SQL was in response to
Aldian's question :)
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CAENN6BLoBbu8v_BTDaPYyaQ1AJzQGi2E9t1trg_9PwbzP-Rn3A%40mail.gmail.com.
nm
Apr 15, 2019, 6:57:07 AM4/15/19
to Django users
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
Reply all
Reply to author
Forward
0 new messages