How can I get all Django groups and optionally get associated user record?

Question

This should be pretty simple since it's very simple with pure SQL. I have the following query which gets exactly what I want in Django:

SELECT auth_group.id, auth_group.name, auth_user.username
FROM auth_group
LEFT JOIN auth_user_groups
  ON auth_group.id = auth_user_groups.group_id
LEFT JOIN auth_user
  ON auth_user_groups.user_id = auth_user.id
WHERE auth_user.id = 3
  OR auth_user.id IS NULL;

How can I get this result using the ORM properly?

Answer 1

You can query with:

from django.contrib.auth.models import Group
from django.db.models import F, Q

Group.objects.filter(
    Q(user=None) | Q(user=3)
).annotate(
    username=F('user__username')
)

The Group objects that arise from this queryset will have an extra attribute .username with the name of the user. Although this will introduce likely a lot of duplicate data, since the username will be either None (NULL) or the username of the user with id=3.