[Fixed]-Django filter table and sort by count

by

1👍

✅

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
👤solarissmoke

Leave a comment