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

Using Django Using the ORM
1

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:

not-enrolled-into-all
not-enrolled-into-all1215×607 85.2 KB

I can do this in SQL with the following statement:

SELECT member.id as id,
       firstname,
       surname
FROM member
    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!

2

Should look something like this:

Member.objects.annotate(enrolment_count=Count("enrolment_set").filter(enrolment_count__lt=4)

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.

3

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?:

Member.objects.prefetch_related('gamembers').annotate(gamecount=Count('gamembers')).filter(gamecount__lt=4)

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

4

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.

5

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.