django orm optimize the query

Question

I need query in django orm to clubs list endpoint. with clubs where user is the club owner, or user is Tournament Referee (with empty end_date, or the end_date is more recent than now).

I have models:

class User(AbstractBaseUser):
    id = models.UUIDField(default=uuid.uuid4, primary_key=True, max_length=128)
    ...

class Club(models.Model):
    id = models.UUIDField(default=uuid.uuid4, primary_key=True, max_length=128)
    owner = models.ForeignKey(User, on_delete=models.SET_NULL, blank=True, null=True)

class Tournament(models.Model):
    id = models.UUIDField(default=uuid.uuid4, primary_key=True, max_length=128)
    club = models.ForeignKey(Club, on_delete=models.CASCADE)

class TournamentReferee(models.Model):
    id = models.UUIDField(default=uuid.uuid4, primary_key=True, max_length=128)
    tournament = models.ForeignKey(Tournament, on_delete=models.CASCADE, related_name='tournament_referee')
    user = models.ForeignKey(User, on_delete=models.CASCADE)
    end_date = models.DateTimeField("access expire date", blank=True, null=True)

I created my query like this, but maybe there is a better way to do it with a single query?

    def get_queryset(self):
        if self.request.method == 'GET':
            now = datetime.datetime.now()
            referee_clubs_list = TournamentReferee.objects.filter(Q(user=self.request.user), (Q(end_date__isnull=True) | Q(end_date__gte=now))).values_list('tournament__club_id')
            return Club.objects.filter(Q(owner=self.request.user) | Q(id__in=(referee_clubs_list)))
        return super().get_queryset()

Answer 1

You can query with

from django.db.models import Q
from django.utils.timezone import now


def get_queryset(self):
    if self.request.method == 'GET':
        return Club.objects.filter(
            Q(owner=self.request.user)
            | (
                Q(tournament__tournament_referee__user=request.user)
                & (
                    Q(tournament__tournament_referee__end_date=None)
                    | Q(tournament__tournament_referee__end_date__gte=now())
                )
            )
        ).distinct()
    return super().get_queryset()

this will make LEFT OUTER JOINs the tables of the Tournament and TournamentReferee models. The .distinct() ^[Django-doc] call guarantees that the Club item will occur at most once.