Django annotate queryset by field from m2m through model

Question

I have models BottleType and OrganisationBottleType. And i want to annotate BottleType queryset by fields 'is_accepted' and 'points' from OrganisationBottleType.

OrganisationBottleType model:

class OrganisationBottleType(models.Model):
    organisation = models.ForeignKey(
        'Organisation',
        related_name='organisation_bottle_types',
        on_delete=models.CASCADE,
    )
    bottle_type = models.ForeignKey(
        'machines.BottleType',
        related_name='organisation_bottle_types',
        on_delete=models.CASCADE,
    )

    is_accepted = models.BooleanField(...)
    points = models.FloatField(...)

Suppose I have organisation_id and BottleType queryset, so for each object from queryset need to find OrganisationBottleType filtered by bottle_type and organisation and annotate fields 'is_accepted' and 'points'.

(When filtered OrganisationBottleType qs is found can just take first object from it, because it is assumed that the couple of fields: organisation - bottle_type is unique)

My idea is to use Subquery in annotation but i can't do it right.

I will be grateful for advice!

Answer 1

If i understand you correctly, for each bottle type you want to:

• find its points
• check if it's accepted.

There are two ways of solving your problem:

1. Fetch OrganisationBottleType queryset and match OrganisationBottleType objects to corresponding objects in BottleType in python with help of .prefetch_related() and Prefetch() object
2. Annotate BottleType queryset with corresponding objects from OrganisationBottleType with help of .annotate() and Subquery()

Both options are described below:

1. Use .prefetch_related with Prefetch object

Given:

• queryset - BottleType queryset
• organisation_id - id of needed organisation

Solution:

queryset = queryset.prefetch_related(
    Prefetch(
        'organisation_bottle_types', 
        queryset= OrganisationBottleType.objects.filter(organisation_id=organisation_id)
    )
)

After that you can retrieve desirable data in a such way:

for bottle_type in queryset:
    if bottle_type.organisation_bottle_types.all():
        related_object = bottle_type.organisation_bottle_types.all()[0]
        is_accepted = related_object.is_accepted
        points = related_object.points
    else:
        is_accepted = False
        points = None

2. Use SubQuery

Given:

• queryset - BottleType queryset
• organisation_id - id of needed organisation

Solution:

 organisation_bottle_types = OrganisationBottleType.objects.filter(organisation_id=organisation_id, bottle_type=OuterRef('id'))

queryset = queryset.annotate(
    is_accepted=Subquery(organisation_bottle_types.values('is_accepted')[:1])
).annotate(
    points=Subquery(organisation_bottle_types.values('points')[:1], output_field=BooleanField())
)

After you can do:

for bottle_type in queryset:
    is_accepted = bottle_type.is_accepted
    points = bottle_type.points

Resume:

Personally, i'd go with second options, because it would do all matching logic at database level, rather than at code level.

The first option is better, when you need to match whole object, not just a several fields (like points and is_accepted from your question)