James James - 4 months ago 21
SQL Question

Django filter table and sort by count

I am trying to get rows from a table whose entries have the most matches in another table, referenced with a manytomany field.

The SQL that works is:

SELECT *, COUNT(*) FROM events_event_genres
LEFT JOIN events_event
ON event_id = events_event.id
WHERE genre_id IN (6,9,31)
AND start_date > '2016-08-06'
GROUP BY event_id
ORDER BY COUNT(*) DESC
LIMIT 10


The event model has:

class Event(models.Model):
title = models.CharField(max_length=250)
start_date = models.DateTimeField()
genres = models.ManyToManyField("genres.Genre", blank=True, null=True, related_name="events")


In my view I have tried:

results = Event.objects.filter(
Q(genres__id__in=genres),
Q(start_date__gte=datetime.date.today()) \
.annotate(event=Count('id')) \
.order_by('event')


The results I am getting are almost the same as this SQL query:

SELECT * FROM events_event
LEFT JOIN events_event_genres
ON events_event.id = event_id
WHERE genre_id IN (6,9,31)
AND start_date > '2016-08-06'
LIMIT 10


So clearly the count is not working.

What is the django way to do this?

Answer

You are not far off. This query should work:

Event.objects.filter(genres__in=genres, start_date__gte=datetime.date.today())\
.distinct().annotate(num_genres=Count('genres')).order_by('-num_genres')

Each Event object in the resulting queryset will have a num_genres property with a count of the number of genres that matched. They will be ordered by the number of matching genres.

The resulting SQL is:

SELECT DISTINCT "events_event"."id", "events_event"."name", 
COUNT("events_event_genres"."genre_id") AS "n" 
FROM "events_event" INNER JOIN "events_event_genres" 
ON ( "events_event"."id" = "events_event_genres"."event_id" ) 
WHERE "events_event_genres"."genre_id" IN (g1) 
GROUP BY "events_event"."id", "events_event"."name" 
ORDER BY "n" DESC