Django ORM prefetch field from manual join table on many-to-many relationship

user_222163 0
Viewed 106

I'm facing an n-plus-1 query explosion after changing a Django model and I think it is because I am having to re-query for fields from a manual join table

As a stripped-down example

class Dashboard(models.Model):
    team: models.ForeignKey = models.ForeignKey("Team", on_delete=models.CASCADE)
    items = models.ManyToManyField("Item", related_name="dashboards", through="DashboardItem")

class Item(models.Model):
    deleted: models.BooleanField = models.BooleanField(default=False)

class DashboardItem(models.Model):
    class Meta:
        unique_together = (
            "dashboard",
            "item",
        )

    dashboard = models.ForeignKey(Dashboard, on_delete=models.CASCADE)
    item = models.ForeignKey("Item", on_delete=models.CASCADE)
    combo_hash: models.CharField = models.CharField(max_length=400, null=True, blank=True)

If I'm querying for a dashboard and know I need to see its items with only 2 queries I can do this

dashboards = Dashboard.objects.filter(items__deleted==False).select_related("team").prefetch_related('items')

but later in the code, despite having the opportunity to prefetch on dashboard or item. I find I'm needing to do

DashboardItem.objects.filter(item=item,dashboard=dashboard).first().combo_hash

How do I prefetch values on the manual through table when I load the dashboard or item?

many-to-many django-orm django-models django
1 Answers

You can prefetch on your custom through table the same way you do it for reverse foreign key relations, so by using dashboarditem_set:

dashboards = Dashboard.objects.select_related(
    "team"
).prefetch_related(
    "items",
    "dashboarditem_set",
)

for d in dashboards:
    combo_hash = d.dashboarditem_set.all()[0].combo_hash

It will be the same usage with your Item model.

user_67598440
Related