How to get data from both sides of a many to many join in django

Let's say I have the following models:

class Well(TimeStampMixin, models.Model):
    plate = models.ForeignKey(Plate, on_delete=models.CASCADE, related_name="wells")
    row = models.TextField(null=False)
    column = models.TextField(null=False)

    class Meta:
        unique_together = [["plate", "row", "column"]]


class Antibiotic(TimeStampMixin, models.Model):
    name = models.TextField(null=True, default=None)


class WellConditionAntibiotic(TimeStampMixin, models.Model):
    wells = models.ManyToManyField(Well, related_name="well_condition_antibiotics")
    volume = models.IntegerField(null=True, default=None)
    stock_concentration = models.IntegerField(null=True, default=None)
    dosage = models.FloatField(null=True, default=None)
    antibiotic = models.ForeignKey(
        Antibiotic, on_delete=models.RESTRICT, related_name="antibiotics"
    )

In plain english, there are a set of wells and each well can have multiple and many different types of antibiotics.

I'm trying to fetch the data of a given well and all of the antibiotics contained inside it.

I've tried WellConditionAntibiotic.objects.filter(wells__id=1).select_related('antibiotic')

which gives me this query:

SELECT
    "kingdom_wellconditionantibiotic"."id",
    "kingdom_wellconditionantibiotic"."created_at",
    "kingdom_wellconditionantibiotic"."updated_at",
    "kingdom_wellconditionantibiotic"."volume",
    "kingdom_wellconditionantibiotic"."stock_concentration",
    "kingdom_wellconditionantibiotic"."dosage",
    "kingdom_wellconditionantibiotic"."antibiotic_id",
    "kingdom_antibiotic"."id",
    "kingdom_antibiotic"."created_at",
    "kingdom_antibiotic"."updated_at",
    "kingdom_antibiotic"."name"
FROM
    "kingdom_wellconditionantibiotic"
    INNER JOIN "kingdom_wellconditionantibiotic_wells" ON (
        "kingdom_wellconditionantibiotic"."id" = "kingdom_wellconditionantibiotic_wells"."wellconditionantibiotic_id"
    )
    INNER JOIN "kingdom_antibiotic" ON (
        "kingdom_wellconditionantibiotic"."antibiotic_id" = "kingdom_antibiotic"."id"
    )
WHERE
    "kingdom_wellconditionantibiotic_wells"."well_id" = 1

This gives me all of the antibiotic data, but none of the well data. So I tried Well.objects.filter(pk=1).select_related(['well_condition_antibiotics', 'antibiotic']).query which errored.

How can I generate a django query to include all well data and all well antibiotic data?

Building up on your second attempt using Well, you will have to prefetch WellConditionAntibiotic and also select the related antibiotic like this:

from django.db.models import Prefetch


well = Well.objects.filter(pk=1).prefetch_related(
    Prefetch(
        "well_condition_antibiotics",
        queryset=WellConditionAntibiotic.objects.select_related("antibiotic"),
    )
)

Then you can just iterate through the related WellConditionAntibiotic entries with the corresponding antibiotic:

for well_condition_antiobiotic in well.well_condition_antibiotics.all():
    print(well_condition_antiobiotic.antibiotic.name)

You can find more information about prefetch_related and Prefetch here..[Django-doc]

Back to Top