CODEWITHSUNDEEP
pythondjangodjango-modelsdjango-queryset
metersk
metersk

Reputation: 12509

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?

Upvotes: 1

Views: 64

Answers (1)

Brian Destura
Brian Destura

Reputation: 12068

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]

Upvotes: 1

Related Questions