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

Question

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?

sytech · Accepted Answer · 2022-08-18 23:00:00Z

4

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"

edited Aug 18, 2022 at 23:00

answered Aug 18, 2022 at 20:49

sytech

42.7k8 gold badges77 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

anon Over a year ago

Thanks. However, this query creates a JOIN of the tables and I'm looking for a way to run a subquery (my code is more complex than above and there are reasons for this being needed... any way to get the SQL code from my example to run the same way in Django?)

sytech Over a year ago

@user74934 I've added an example that uses subquery specifically. Though, be advised it is probably less performant than the left outer join in the first example.

Brad Martsberger · Accepted Answer · 2022-08-20 15:15:35Z

3

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))
)

answered Aug 20, 2022 at 15:15

Brad Martsberger

1,96714 silver badges7 bronze badges

1 Comment

anon Over a year ago

Thanks that is very good to know! The first answer also covered the question even though the code is more complicated, but it uses regular Django code without additional packages, so I accepted that one but +1 for this package and much simpler syntax!

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related