Get Django to return related objects

Question

I'm trying to filter one table and obtain the related rows from another table as a nested object.

Models are as follow:

class KpiArea(models.Model):
    harvest = models.CharField(max_length=45)
    area_id = models.IntegerField()
    percentage = models.FloatField(max_length=10)
    class Meta:
        managed = False
        db_table = 'kpi_area'
        unique_together = ['harvest', 'area_id']
        
class KpiAreaProducts(models.Model):
    product = models.CharField(max_length=45)
    percentage = models.FloatField(max_length=10)
    area_id = models.IntegerField()
    harvest = models.ForeignKey(KpiArea, on_delete=models.PROTECT, db_column = "harvest")  
    class Meta:
        managed = False
        db_table = "kpi_area_products"
        unique_together = ['harvest', 'area_id']

My goal would be to be able to get the related objects whenever I'm filtering on KpiArea, ideally as a property containing a related object's array. Something like this:

[
    {
        area_id : 1, 
        harvest: '2020/2021', 
        percentage: 25, 
        products: [
            {
                product: 'soy',
                percentage: 15,
                area_id : 1
            },
            {
                product: 'wheat',
                percentage: 35,
                area_id : 1
            },
        ]
    }    
]
  

I've tried linking with 2 foreing keys on KpiAreaProducts model, and unique_together (as per other SO questions) to achieve this.

Could somebody point me in the right direction?

Answer 1

You can use prefetch_related..[Django-doc] with a Prefetch..[Django-doc] like this:

from django.db.models import Prefetch


kpi_areas = KpiArea.objects.prefetch_related(
    Prefetch(
        "kpiareaproducts_set",
        queryset=KpiAreaProducts.objects.only("product", "percentage", "area_id"),
        to_attr="products",
    )
)

for area in kpi_areas:
    for product in area.products:
        print(product)