Optimizing Complicated Queries in Django with Subqueries

This post delves into a Django query optimization technique used for retrieving and annotating challenge data. We’ll break down a complex query that leverages subqueries, conditional logic, and array aggregation to efficiently fetch relevant information.

The Challenge

Imagine you’re building a platform with coding challenges. You need to:

1. Fetch a limited number of active challenges.
2. Annotate each challenge with a flag indicating whether the current user has participated.
3. Include a list of profile photos of the first 5 participants.
4. Order the challenges randomly in production or by endTime in a test environment.

The Solution

Here’s the Django ORM query that accomplishes this:

class StringArray(Subquery):
    template = "ARRAY(%(subquery)s)"
    output_field = ArrayField(base_field=models.TextField())

    challenge_query = (
        Challenges.objects.annotate(
            has_participate=Case(
                When(
                    Exists(UserChallenges.objects.filter(Q(user_id=user_id) & Q(challenge_id=OuterRef("pk")))),
                    then=Value(True),
                ),
                default=Value(False),
                output_field=models.BooleanField(),
            ),
            challengers=StringArray(filtered_challenge.values_list("user__photo", flat=True)[:5]),
        )
        .filter(
            Q(endTime__gte=current_time) if not (SHOW_TEST_COMPANY == "TRUE") else Q(),
        )
        .order_by(""?"" if not (SHOW_TEST_COMPANY == "TRUE") else "-endTime")[:challenge_size]
    )

Let’s break it down piece by piece:

1. StringArray Subquery

class StringArray(Subquery):
    template = "ARRAY(%(subquery)s)"
    output_field = ArrayField(base_field=models.TextField())

This defines a custom StringArray class that inherits from Django’s Subquery. It’s used to aggregate a list of strings into an array within the database. The template specifies the SQL template for creating an array from the subquery results. output_field defines the data type of resulting array.

2. Annotating Participation Status

Challenges.objects.annotate(
    has_participate=Case(
        When(
            Exists(UserChallenges.objects.filter(Q(user_id=user_id) & Q(challenge_id=OuterRef("pk")))),
            then=Value(True),
        ),
        default=Value(False),
        output_field=models.BooleanField(),
    ),

This uses Django’s annotate and Case/When/Exists to add a has_participate field to each Challenge object. It checks if a UserChallenges record exists for the current user and the current challenge (OuterRef("pk")). If it exists, has_participate is set to True; otherwise, it’s False.

3. Fetching Challenger Photos

challengers=StringArray(filtered_challenge.values_list("user__photo", flat=True)[:5]),

This part uses the StringArray subquery to create an array of the first 5 participants’ photos. `filtered_challenge.values_list(