Nested Subquerys in Django

#100 words#django#orm#subqueries

Last week I nailed the most complex query optimisation I have worked on in my career (to date).

First let's set the context. We have a table which includes an inline dropdown to quickly add/remove users to a group. The initial template code was something like this:


<table>
{% for client in clients %}
    ...
    <tr>
    ...
        <td>
            <select>
                <!-- One new database query here -->
                {% for group in user.get_groups %}
                    <!-- Another new database query here -->
                    {% if user in group.users.all %}
                        <option selected>{{group}}</option>
                    {% else %}
                        <option>{{group}}</option>
                    {% endif %}
                {% endfor %}
            </select>
        </td>
    </tr>
    ...
{% endfor %}
</table>

As you can see the for loop triggers one set of N+1 queries, but for each of those, there is another query which probably gets us to NM+1 queries!

The first step here is work out what data we need to get from the database. This is the name of the group (title attribute) and whether a client is a member of the group.

Since we have a list of groups to annotate to each client we can use Django's postgres specific function ArraySubquery which allows this. Next we need to use a JSONObject to allow us to annotate an object with multiple attributes.

Currently this leaves our query looking like this:

  clients = clients.annotate(
      usergroups=ArraySubquery(
          UserGroup.objects.annotate(
              groups=JSONObject(
                  title=F("title"),
                  is_member=????,
              )
          )
      )
  )

The real crux of this optimisation was the is_member attribute of the JSONObject, I stuggled to work out the specific syntax for this and ended up with duplicate entires. However with the help of the community in Discord I got to the solution which is an Exists query on the through table between User and UserGroup which resulted in the following annotation:

  clients = clients.annotate(
      usergroups=ArraySubquery(
          UserGroup.objects.annotate(clientid=OuterRef("pk")).annotate(
              groups=JSONObject(
                  title=F("title"),
                  is_member=Exists(
                      UserGroup.clients.through.objects.filter(
                          usergroup_id=OuterRef("pk"), user_id=OuterRef("clientid")
                      )
                  ),
              )
          )
      )
  )

The key aspect of this query is the first annotation which takes the OuterRef of the first Subquery to allow it be used in the inner subquery. Finally this allows the template to be simplified as follows:

<table>
{% for client in clients %}
    ...
    <tr>
    ...
        <td>
            <select>
                {% for group in client.usergroups %}
                    {% if group.is_member %}
                        <option selected>{{group}}</option>
                    {% else %}
                        <option>{{group.title}}</option>
                    {% endif %}
                {% endfor %}
            </select>
        </td>
    </tr>
    ...
{% endfor %}
</table>
Tomorrow