Django union on annotated request

Question

This is what I want Django to generate in SQL:

select avg(subquery.countval) from ( select count(something) countval,user_id from foo group by user_id ) subquery 

I think this should work with Django based on annotated aggregation documentation:

 Foo.objects.all().values('user_id').\ annotate(countval=Count('id')).\ aggregate(Avg('countval')) 

The problem is that Django 4.x is not generating the correct request. You get something like:

 SELECT FROM (SELECT foo.user_id as user_id,COUNT(foo.id) AS countval from foo group by foo.user_id) 

Any ideas? I was debugging through the source, but it is not obvious what is going wrong.

Answer 1

I could not do this with pure Django code, but this is the best I could do, depending as much as possible on the Django code instead of raw sql.

 from django.db import connection from django.db.models import Count def get_average_count(klass, field_name): foo = klass.objects.values(field_name).annotate(countval=Count('id')) query = "SELECT AVG(subquery.countval) FROM (%s) subquery" % str(foo.query) cursor = connection.cursor() cursor.execute(query) return float(cursor.fetchone()[0]) 

This will execute the exact SQL statement that you said you want to generate. It is also completely independent of the SQL backend you are using and is fully reused (yay DRY) for all classes with the reverse ForeignKey or ManyToMany relationships.

If you really don't want to use raw SQL, another option is to calculate the average in Django:

 from __future__ import division # no need to cast to float now def get_average_count(klass, field_name): counts = klass.objects.values(field_name).annotate(countval=Count('id')).\ values_list('countval', flat=True) return reduce(lambda x, y: x + y / len(counts), counts, 0) 

You might want to check for any performance differences if you plan to have large databases in your database.