How to filter foreign key objects in django queries?

Question

I have two models, one related to other by foreign key like this

class CapturedPrescriptionModel(ColModel):
    p_id = models.IntegerField()
    p_age = models.IntegerField()
    p_gender = models.CharField(max_length=10)
    p_care_type = models.CharField(max_length=100)
    bacteria_id = models.ForeignKey(BacteriaListModel, 
              on_delete=models.CASCADE, null=True)

class SuggestedAntibioticsModel(ColModel):
    prescription_id = models.ForeignKey(CapturedPrescriptionModel, 
                  related_name='antibiotics', 
                   on_delete=models.CASCADE)
    cat_ids = models.TextField()
    flag = models.IntegerField(default=0)

Now I want all the prescriptions with suggested antibiotics where flag=1

I have tried with CapturedPrescriptionModel.objects.filter(antibiotics__flag=1) but that filter the prescriptions not the list of antibiotics in the queryset.

 [
    {
    "id": 7,
    "p_id": 0,
    "p_age": 19,
    "p_gender": "Male",
    "p_care_type": "ICU",
    "bacteria_id": null,
    "antibiotics": [
        {
            "id": 188,
            "cat_ids": "[]",
            "flag": 0,
            "antibiotic_id_id": 87,
            "prescription_id_id": 7
        },
        {
            "id": 187,
            "cat_ids": "[]",
            "flag": 1,
            "antibiotic_id_id": 112,
            "prescription_id_id": 7
        },
      ......
      ]
}
....
]

My expected result will be like this

    [
        {
        "id": 7,
        "p_id": 0,
        "p_age": 19,
        "p_gender": "Male",
        "p_care_type": "ICU",
        "bacteria_id": null,
        "antibiotics": [
            {
                "id": 187,
                "cat_ids": "[]",
                "flag": 1,
                "antibiotic_id_id": 112,
                "prescription_id_id": 7
            }
          ]
}
....
]

Do you want all prescriptions (even those without antibiotics__flag=1), but the antibiotics set should contain only those with flag=1? — Endre Both
– Endre Both, Commented Apr 12, 2019 at 8:07
Yes, for those prescription for which any antibiotics__flag=1 does not suffice should be a empty list — sankycse
– sankycse, Commented Apr 12, 2019 at 8:09

Endre Both · Accepted Answer · 2019-04-12 08:16:44Z

2

You need a filtered Prefetch if you want to filter the related objects only, not the main objects:

from django.db.models import Prefetch

CapturedPrescriptionModel.objects.prefetch_related(Prefetch(
    'antibiotics',
    queryset=SuggestedAntibioticsModel.objects.filter(flag=1)
)

You then have to make sure that antibiotics on the individual prescription objects is only accessed with prescription.antibiotics.all(), otherwise the prefetch is not used and you'll get all antibiotics again.

answered Apr 12, 2019 at 8:16

Endre Both

5,8201 gold badge30 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bloodmallet · Accepted Answer · 2019-04-12 08:14:40Z

Collect all Prescriptions:

prescriptions = CapturedPrescriptionModel.objects.all()

for prescription in prescriptions:
    prescription.antibiotics = prescription.antibiotics.filter(flag=1)

# at this time presciptions should be prepared, just make sure to not save them...

You could also extend your model to have a property for that list.

class CapturedPrescriptionModel(ColModel):
    p_id = models.IntegerField()
    p_age = models.IntegerField()
    p_gender = models.CharField(max_length=10)
    p_care_type = models.CharField(max_length=100)
    bacteria_id = models.ForeignKey(BacteriaListModel, 
              on_delete=models.CASCADE, null=True)

    @property
    def flagged_antibiotics(self):
        try:
            return self.antibiotics.filter(flag=1)
        except Exception:
            return []

class SuggestedAntibioticsModel(ColModel):
    prescription_id = models.ForeignKey(CapturedPrescriptionModel, 
                  related_name='antibiotics', 
                   on_delete=models.CASCADE)
    cat_ids = models.TextField()
    flag = models.IntegerField(default=0)

Something like this would be my first take on that

Collectives™ on Stack Overflow

How to filter foreign key objects in django queries?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related