Django annotate queryset with list from related model

Question

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

If you iterate over the assets queryset, you can lookup asset.software_set.all() to obtain all related Software objects. You can use .prefetch_related(..) to do the JOINing in "bulk" in memory, to avoid making an extra query to the database per Asset. — willeM_ Van Onsem
– willeM_ Van Onsem, Commented Jul 24, 2020 at 17:25

willeM_ Van Onsem · Accepted Answer · 2020-07-24 17:56:49Z

1

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()))

answered Jul 24, 2020 at 17:56

willeM_ Van Onsem

482k33 gold badges483 silver badges624 bronze badges

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

3 Comments

AJwr Over a year ago

Just saw your comment – this doesn't work. I get AttributeError: 'Asset' object has no attribute 'software_set', could this be because both tables are not managed by django?

willeM_ Van Onsem Over a year ago

@AJwr: the fact that these are not managed does not matter. The name of the relation (in reverse or not) does not depend on the database, it is a "conceptual layer" that Django builts on top of that. Is the name of the model (unmanaged or not) Software, did you specify a related_name in the ForeignKey? Perhaps it is better to edit the question and show the full details of the model classes.

AJwr Over a year ago

Ah my mistake! I was trying it on an old machine that had not run the migration, this works well thank you

Collectives™ on Stack Overflow

Django annotate queryset with list from related model

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related