Django query to sort by a field on the latest version of a Many to Many relationship

user_6611672 0
Viewed 137

Let's say I have the following Django models:

class Toolbox(models.Model):
    class Meta:
      constraints = [
          models.UniqueConstraint(
              fields=["name", "version"],
              name="%(app_label)s_%(class)s_unique_name_version",
          )
      ]

    name = models.CharField(max_length=255)
    version = models.PositiveIntegerField()
    tools = models.ManyToManyField("Tool", related_name="toolboxes")

    def __str__(self) -> str:
        return f"{self.name}"

class Tool(models.Model):
    name = models.CharField(max_length=255)

    def __str__(self) -> str:
        return f"{self.name}"

I want to write a query that fetches all Tools and returns them sorted by their latest toolbox's name. I know I can achieve this using the following code:

tools = Tool.objects.all()
for tool in tools:
    tool.latest_toolbox = tool.toolboxes.order_by("-version").first()

tools = sorted(tools, key=lambda x: x.latest_toolbox.name)

Here's a unit test written in pytest-django to prove this works:

from pytest_django.asserts import assertQuerysetEqual

def test_sort_tools_by_latest_toolbox_name():
    tool1 = Tool.objects.create(name="Tool 1")
    tool2 = Tool.objects.create(name="Tool 2")
    toolbox1_v1 = Toolbox.objects.create(name="A", version=1)
    toolbox1_v1.tools.add(tool1)
    toolbox1_v2 = Toolbox.objects.create(name="Z", version=2)
    toolbox1_v2.tools.add(tool1)
    toolbox2_v1 = Toolbox.objects.create(name="B", version=1)
    toolbox2_v1.tools.add(tool2)

    tools = Tool.objects.all()
    for tool in tools:
        tool.latest_toolbox = tool.toolboxes.order_by("-version").first()
    
    tools = sorted(tools, key=lambda x: x.latest_toolbox.name)
    assertQuerysetEqual(tools, [tool2, tool1])

However, the Tool table has thousands of records and this is taking minutes to execute. Is there a faster query I can write?

I've tried the following but it's returning duplicates and isn't sorting the tools correctly:

Tool.objects.order_by("toolboxes__name")
# <QuerySet [<Tool: Tool 1>, <Tool: Tool 2>, <Tool: Tool 1>]>
django-queryset django-orm django python
2 Answers

Try this approach using Subquery and OuterRef:

from django.db.models import OuterRef, Subquery

toolbox_subquery = Toolbox.objects.filter(tools=OuterRef('pk')).order_by('-version')
tools_qs = Tool.objects.order_by(Subquery(toolbox_subquery.values('name')[:1]))

If you need the name of the latest toolbox other than for ordering, you can just put it in an annotated field:

tools_qs = Tool.objects.annotate(latest_toolbox_name=Subquery(toolbox_subquery.values('name')[:1])).order_by('latest_toolbox_name')

Each tool will then have an annotated field latest_toolbox_name that will have the name of their associated toolbox with the latest version.

user_67598440

Use prefetch_related

toolbox_qs = Toolbox.objects.order_by("-version")
tools_qs = Tool.objects.prefetch_related(Prefetch('toolboxes', query=toolbox_qs)
for tool in tools_qs:
    tool.latest_toolbox = tool.toolboxes.first()

or i think

tools_qs = Tool.objects.prefetch_related('toolboxes')
for tool in tools_qs:
    tool.latest_toolbox = tool.toolboxes.order_by("-version").first()

Not sure either of those will work out of the box but along those lines will work, basically instead of querying every Tools toolboxes, you prefetch all of the relations and then filter through them and thus doing 1 or 2 queries instead of many

user_157920080
Related