I have two tables named “member” and “enrolment”. The first holds names of members registered to a Games Club while the second holds their enrolment into the club games. The club offers four(4) games: chess, tennis, swimming and snooker. I would like to select members that have not enrolled into all four games (i.e. matches 0-to-3 games). See illustration:
I can do this in SQL with the following statement:
SELECT member.id as id,
LEFT JOIN enrolment
ON memberfk = member.id
GROUP BY member.id
HAVING COUNT(member.id) < 4;
I would appreciate the same query using Django’s ORM syntax for learning purposes. I am already familiar with
objects.all(), objects.filter(), queryset.select_related(), queryset.prefetch_related(). I am struggling to wrap my head around the group and count part. Help!
Should look something like this:
Although I’ll admit I’m not 100% sure the method needed here is
annotate: it might be
aggregate, but the overall query structure should be the one I wrote above.
This threw an error but it was a close shot (after fixing the missing closing parenthesis). At least it motivated an extra study into
aggregate(). I now have some understanding of how they work. The result?:
gamembers is the
related_name for the foreignkey relationship defined in
Enrolment model (otherwise, that would be ‘
memberfk_set’ by default (correct me if I am wrong here).
I wonder if there is a way to return django’s QuerySets directly from SQL statements since django’s
prefetch_related() hits the database twice whereas the SQL verison above does it once. Otherwise, there’s got to be another way to achieve the same level of optimization using django’s ORM.
You don’t need prefetch related for this query because you’re not accessing the individual rows from that other table. Remove prefetch related and you’ll see this is one query.