Get extra field via annotate in Django many to many relation

Question

I have a m2m relation between a Feature model and the User model through an intermediary table. Feature model represents all the available features, and a User can enable or disable zero, one or more of them via web or api. When a user enables a feature the corresponding record is created in the intermediary table, when the user disables a feature the record is deleted.

from django.contrib.auth.models import User

class Feature(models.Model):
    name = models.CharField(max_length=50)
    user = models.ManyToManyField(User, through='FeatureUserM2M')
    ...other fields...

class FeatureUserM2M(models.Model):
    user = models.ForeignKey(User, on_delete=models.CASCADE)
    feature = models.ForeignKey(Feature, on_delete=models.CASCADE)
    ...other fields...

Supposing data like so

Feature            User          Intermediary
|id|name        |  |id|name   |  |id|user_id|feature_id|
+--+------------+  +--+-------+  +--+-------+----------+
|1 |feature foo |  |1 |user A |  |1 |2      |1         |
|2 |feature bar |  |2 |user B |  |  |       |          |
|3 |feature baz |  |  |       |  |  |       |          |

I need a result with all the available features, and for a given user, another field indicating if the user has the feature enabled or not (could be a boolean). Ideally, for "user A", the result I need is like:

|user_id|feature_id|enabled |
+-------+----------+--------+
|2      |1         | true   |
|2      |2         | false  |
|2      |3         | false  |

I tried

Feature.objects.annotate(enabled=Subquery(
    FeatureUserM2M.objects.filter(
        feature=OuterRef('pk'),
        user=request.user,
    )
))
*** django.core.exceptions.FieldError: Cannot resolve expression type, unknown output_field

I don't think the query I did is right, but even specifying the output type as BooleanField I don't have what I want.

Feature.objects.annotate(enabled=Subquery(
    FeatureUserM2M.objects.filter(
        feature=OuterRef('pk'),
        user=request.user,
    )), output_field=BooleanField
)
*** TypeError: QuerySet.annotate() received non-expression(s): <class 'django.db.models.fields.BooleanField'>.

Swift · Accepted Answer · 2022-11-07T22:03:13.040

Seems like you can just use an Exists() subquery. An example and description of it is in the documentation here:

Subqueries - Exists

Feature.objects.annotate(enabled=Exists(
    FeatureUserM2M.objects.filter(
        feature=OuterRef('pk'),
        user=request.user,
    )
))

However, you were pretty close to the correct answer, all I did was read a bit further into the docs and replaced your Subquery with Exists :)

The reason your second attempt resulted in an error which could have been correct, would be fixed like so:

Feature.objects.annotate(enabled=Subquery(
    FeatureUserM2M.objects.filter(
        feature=OuterRef('pk'),
        user=request.user,
    ), output_field=BooleanField) # <-- moved the parenthesis
)

This is described in the documentation here:

Subqueries

Where you can specify the keyword argument output_field as you were trying to do, but actually you placed it inside the .annotate(...)

But im not entirely convinced it would be the same resulting output as Exists(...) though having said that.

Get extra field via annotate in Django many to many relation

1 Answers1