Need the Django equivalent of an SQL query: For learning purposes.

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 as id,
FROM member
    LEFT JOIN enrolment
        ON memberfk =

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 annotate() and aggregate(). I now have some understanding of how they work. The result?:


where 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.