CODEWITHSUNDEEP
djangosubquery
anon
anon

Reputation:

Using a subquery to get a COUNT from a different table in Django

I have these models:

class IceCream(models.Model):
    name = models.CharField(max_length=255)

class Topping(models.Model):
    name = models.CharField(max_length=255)
    ice_cream = models.ForeignKey(IceCream, on_delete=models.CASCADE, related_name="toppings")
    is_active = models.BooleanField(db_index=True)

I would like to run the following query to get a list of all ice creams, together with a count of the total number of available toppings, which in SQL would look like this:

SELECT ice_cream.*, 
   (SELECT COUNT(id) FROM topping WHERE topping.ice_cream = ice_cream.id AND topping.is_active = True) AS total_toppings
FROM ice_cream

This is a simplified version and there are more parameters in there, but if I can make this work, then things should work out. Can I do this in Django using Subquery expressions? I have not managed to make it work. Or are raw queries my only way out here because of the COUNT that I want to include in the subquery?

Upvotes: 0

Views: 951

Answers (2)

Brad Martsberger
Brad Martsberger

Reputation: 1967

The django-sql-utils package makes this easy, pip install django-sql-utils and then

from sql_util.utils import SubqueryCount

queryset = IceCream.objects.all().annotate(
    total_toppings=SubqueryCount('toppings', filter=Q(is_active=True))
)

Upvotes: 3

sytech
sytech

Reputation: 41041

You can use annotate for this, which supports aggregation functions like Count. You can also add filters to aggregation functions by providing a Q object.

# ...
from django.db.models import Count, Q
# ...

qs = IceCream.objects.all().annotate(
    active_toppings_count=Count("toppings", filter=Q(toppings__is_active=True))
)

for ice_cream in qs:
    print(ice_cream.name, ice_cream.active_toppings_count)

This uses a Left (outer) join to produce the results. This is (likely) better for performance than using a subquery.

If you must use a subquery, you can do the following:

from django.db.models import F, Func, OuterRef, Subquery
#...

subq = (
    Topping.objects.filter(is_active=True, ice_cream=OuterRef("pk"))
    .annotate(count=Func(F("id"), function="Count"))
    .values("count")
)
qs = IceCream.objects.annotate(active_toppings_count=Subquery(subq))

Then the resulting SQL is equivalent to your raw SQL example:

SELECT "myapp_icecream"."id",
       "myapp_icecream"."name",
       (SELECT Count(U0."id") AS "count"
        FROM   "myapp_topping" U0
        WHERE  ( U0."ice_cream_id" = ( "myapp_icecream"."id" )
                 AND U0."is_active" )) AS "active_toppings_count"
FROM   "myapp_icecream"

Upvotes: 4

Related Questions