Sum numeric values from different tables in one query

user_35070 0
Viewed 408

In SQL, I can sum two counts like

SELECT (
  (SELECT count(*) FROM a WHERE val=42)
  +
  (SELECT count(*) FROM b WHERE val=42)
)

How do I perform this query with the Django ORM?

The closest I got is

a.objects.filter(val=42).order_by().values_list('id', flat=True).union(
    b.objects.filter(val=42).order_by().values_list('id', flat=True)
).count()

This works fine if the returned count is small, but seems bad if there's a lot of rows that the database must hold in memory just to count them.

django-orm django python
1 Answers

Your solution can be only little simplified by values('pk') instead of values_list('id', flat=True), because this would affect only a type of rows of the output, but the source SQL of both querysets is the same:

SELECT id FROM a WHERE val=42 UNION SELECT id FROM b WHERE val=42

and the method .count() makes only a query around a subquery:

SELECT COUNT(*) FROM (... subquery ...)

It is not necessary that a database backend would hold all values in memory. It can also only count them and forget. (not checked)

Similarly if you run a simple SELECT COUNT(id) FROM a, it doesn't need to collect id.

Subqueries of the form SELECT count(*) FROM a WHERE val=42 in a bigger query are not possible because Django doesn't use lazy evaluation for aggregations and immediately evaluates them.

The evaluation can be postponed e.g. by grouping by some expression that has only one possible value, e.g. GROUP BY (i >= 0) (or by an outer reference if it would work), but the query plan can be worse.

Another problem is that a SELECT is not possible without a table. Therefore I will use an unimportant row of an unimportant table in the base of query.

Example:

qs = Unimportant.objects.filter(pk=unimportant_pk).values('id').annotate(
    total_a=a.objects.filter(val=42).order_by().values('val')
        .annotate(cnt=models.Count('*')).values('cnt'),
    total_b=b.objects.filter(val=42).order_by().values('val')
        .annotate(cnt=models.Count('*')).values('cnt')
)

It is not nice, but it could be easily parallelized

SELECT
    id,
    (SELECT COUNT(*) AS cnt FROM a WHERE val=42 GROUP BY val) AS total_a,
    (SELECT COUNT(*) AS cnt FROM b WHERE val=42 GROUP BY val) AS total_b
FROM unimportant WHERE id = unimportant_pk

Django docs confirms that simple solution doesn't exist.

Using aggregates within a Subquery expression
...
... This is the only way to perform an aggregation within a Subquery, as using aggregate() attempts to evaluate the queryset (and if there is an OuterRef, this will not be possible to resolve).

user_4484740
Related