CODEWITHSUNDEEP
pythondjangodjango-queryset
AJwr
AJwr

Reputation: 618

Django annotate queryset with list from related model

This is my first time using Django querysets in such detail so I am a little confused.

I have two models:

Asset (Django model but not managed by Django):

id = models.BigIntegerField(primary_key=True, db_index=True, editable=False)
asset = models.CharField(
    max_length=255, null=False
)
ip = models.CharField(
    max_length=255,
    null=True,
)
entity = models.ForeignKey(
    Entity,
    on_delete=models.CASCADE,
    related_name="owned_assets",
    db_constraint=False,
)

Software (same as above – not managed by Django):

id = models.BigIntegerField(primary_key=True, db_index=True, editable=False, null=False)
entity = models.ForeignKey(
    Entity,
    db_constraint=False,
    null=False,
)
asset = models.ForeignKey(
    Asset, db_constraint=False, null=False
)
software = models.CharField(
    max_length=64, null=False
)
version = models.CharField(
    max_length=64, null=False
)

When a user GETs all assets, I want to decorate the Asset queryset with the related Software. A software entry is not unique across asset and entity though, a single asset can have multiple software entries associated with it. What would be the best way to go about annotating the base Asset queryset with these software entries? How do I add a list of software and version to a single Asset in the queryset?

Is it possible to do this in the DB and not in memory? Thank you

Upvotes: 1

Views: 2020

Answers (1)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 476557

You can access the relation in reverse with asset.software_set.all(), for example:

assets = Asset.objects.all()
for asset in assets:
    print((asset, asset.software_set.all()))

But this is rather inefficient, since for N Asset objects, it will make N+1 queries to the database. First a query to fetch all the Assets, and then a query per asset to fetch the related software_set.all() objects.

You can better make use of .prefetch_related(…) [Django-doc] to fetch all related objects in memory with one extra query, and then JOIN at the Django/Python layer, so:

assets = Asset.objects..prefetch_related('software_set')
for asset in assets:
    print((asset, asset.software_set.all()))

Upvotes: 1

Related Questions