Django filter and annotate confusion

Question

I have two models

model A:
   cid = ...
   rating = ...

model B:
   id = ... (this id is same as cid in model A. But no foreign key directly)
   name = ....

now I need the list of all modelB objects whose rating is greater than 3.

I also need the rating field to be available in the result queryset.

I have done the following,

good_rating_A_queryset = A.filter(rating__gt=3)

good_rating_B_queryset = B.filter( id__in = good_rating_A_queryset.values("cid") )

Now I have all B objects whose ratings are greater than 3.

Now I want to retain the rating field from good_rating_A_queryset in good_rating_B_queryset so that I can serialize it and send to front-end.

I am using DRF to serialize. I want to access the rating like good_rating_B_queryset.first().rating

I thought I could use annotate but can't figure out how to map the field

pls help

Answer 1

I agree with @WillemVanOnsem, it is better to use OneToOne or ForeignKey when defining two interdependent Models. That's why relations exists in the Database.

Other than that, if you still need rating from another table, then you can use Subquery for that. Like this:

from django.db.models import OuterRef, Subquery

good_rating_A_queryset = A.filter(pk=OuterRef('pk')).values('rating')
good_rating_B_queryset = B.annotate(rating=Subquery(good_rating_A_queryset[:1])).filter(rating__gt=3)