Show two distinct column count in annotate Django

Question

I'm getting trouble with annotate in Django.

I have two models in projects. (just structure)

model A (field:name(char))
model B (field:A(A; related_name="b"), user(user), content(char; null=True))

The data of model B is just like...

row1 -> A:1, user:2, content:"content1"
row2 -> A:1, user:3, content:"content2"
row3 -> A:1, user:2, content:None
row4 -> A:2, user:1, content:None
row5 -> A:1, user:3, content:None

My intention is a unique value that combines user and content according to the pk of model A being searched.

If I look up pk:1 of Model A and the answer I want is 2, and the reason is as follows.

row1 -> A:1, user:2
row2 -> A:1, user:3

So the orginal code was...

subquery = B.objects.filter(a=pk).distinct('user').count()
A.objects.annotate(count=Value(subquery)).get(pk=pk)

However, it is inefficient because it brings up one more query, not inner join.

What should I do?

Answer 1

You can move focus to the B model instead:

B.objects.filter(a_id=1).values('user', 'content').distinct().count()