CODEWITHSUNDEEP
sqldjangosortingcountmany-to-many
James
James

Reputation: 93

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?

Upvotes: 0

Views: 107

Answers (1)

solarissmoke
solarissmoke

Reputation: 31404

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

Upvotes: 1

Related Questions