queryset: LEFT OUTER JOIN with extra conditions in django ORM

[Michael]

Hello,

When doing the following, the LEFT OUTER JOIN does not work as expected: not all the Users are in the result.

qs = User.objects.all().filter(modela__deleted_at__isnull=True).annotate(modela__count=models.Count('modela', distinct=True))

Here is the simplified version of the query generated by django:

SELECT COUNT(DISTINCT "modela"."id") AS "modela__count", "users".*
FROM "users"
LEFT OUTER JOIN "modela" ON ( "users"."id" = "modela"."user_id" )
WHERE "modela"."deleted_at" IS NULL
GROUP BY "users"."id"

The problem comes from the WHERE clause makes.

Indeed, there is a LEFT JOIN but the later WHERE conditions forced it to be a plain JOIN. I need to pull the conditions up into the JOIN clause to make it work as intended.
So, instead of

LEFT OUTER JOIN "modela" ON ( "users"."id" = "modela"."user_id" )
WHERE "modela"."deleted_at" IS NULL

I need the following which works.

LEFT OUTER JOIN "modela" ON ( "users"."id" = "modela"."user_id" ) AND "modela"."deleted_at" IS NULL

How can I change the queryset to get this?

I'd like to avoid the raw query.

I tried another solution using Case, When, etc but I could not make it work properly either.

The slight change for the LEFT OUTER JOIN seems to be the more straightforward solution anyway.

Thanks

[Michael]

Just to give more information about the solution using Case, When, etc.

Here is what I tried:

qs = User.objects.all().annotate(modela__count=models.Count(Case(When(modela__deleted_at__isnull=True, then=1))))

which turns into the following SQL query:

SELECT COUNT(CASE WHEN "modela"."deleted_at" IS NULL THEN 1 ELSE NULL END) AS "modela__count", "users".*

FROM "users" LEFT OUTER JOIN "modela" ON ( "users"."id" = "modela"."user_id" )

GROUP BY "users"."id"

By doing that, I get all the users but I get "1" (instead of 0) for "modela__count" for all the users who don't have any ModelA at all.

Why?

Thanks

[James Schneider]

Have you tried taking out the .all()? You don't need that if you're using filter(). I don't think that will help though.

-James

--
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 http://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/9a5c2554-71d4-4a13-a611-415ec11cd0f0%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Michael Palumbo]

Yes, I tried removing the all() but that does not change anything.

--
You received this message because you are subscribed to a topic in the Google Groups "Django users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/django-users/jH7gUwhwLt4/unsubscribe.
To unsubscribe from this group and all its topics, send an email to django-users...@googlegroups.com.

To post to this group, send email to django...@googlegroups.com.
Visit this group at http://groups.google.com/group/django-users.

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CA%2Be%2BciVz5cf%3DTN8O59RF--15Oz6NEJy3vF5kAiR2OntZoNuXjg%40mail.gmail.com.