Annotating a queryset with objects from a different "filter" queryset

Question

If you're filtering a queryset using values from a different queryset, what is the best way to annotate the "filter" value onto the original queryset?

For example, below I have a simGrants QS that I'm filtering using the simKeys QS. When a match is found, I'd like to append the matching simKeys cosine score to the simGrants object.

The database is fairly large so I'm looking to find the most computationally efficient way to do this.

Simkeys =similarity_matrix.objects.filter(y_axis__in=profiles).filter(cosine_score__gt=0)
simGrants = Grant.objects.filter(indexKey__in=simKeys)

willeM_ Van Onsem · Accepted Answer · 2019-06-18 21:31:44Z

1

You can annotate your Grant object with the fields from the related model with an F-expression [Django-doc]. This results in a query that looks like:

from django.db.models import F

Grant.objects.annotate(
    cosine_score=F('similarity_matrix__cosine_score')
).filter(
    similarity_matrix__y_axis__gt=0,
    similarity_matrix__cosine_score__gt=0
)

The Grant objects that arise from this queryset will have an extra attribute cosine_score that is the cosine_score of the related similarity_matrix object.

answered Jun 18, 2019 at 21:31

willeM_ Van Onsem

482k33 gold badges483 silver badges624 bronze badges

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

1 Comment

Rex Over a year ago

Thank you so much! This works perfectly, I'll have to read up of F-expressions a bit more in the future.

Collectives™ on Stack Overflow

Annotating a queryset with objects from a different "filter" queryset

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related