CODEWITHSUNDEEP
djangodjango-orm
Ernst
Ernst

Reputation: 514

Exclude related objects before Count

For example I've got 3 models User, A, B.

class A(models.Model):
    creator = models.ForeignKey(
        'users.User', on_delete=models.CASCADE, related_name='A_set'
    )


class B(models.Model):
    user = models.ForeignKey(
        'users.User', on_delete=models.CASCADE, related_name='B_set'
    )
    a_model = models.ForeignKey(
        'a.A', on_delete=models.CASCADE, related_name='B_set'
    )

I would like to get count of B where user isn't a creator of a_model object.

I've tried query: 

`User.objects.prefetch_related('B_set').last().B_set.exclude(a_model__creator=F('user')).count()`

Here is my try with annotation:

User.objects.annotate(b_count=Count('B_set', filter=(~Q(B_set__A__creator=F('user')))))

But I am getting an error:

Cannot resolve keyword 'user' into field.

And then it suggest me fields that relate to User. Also I tried to change user to B_set__user with F() but it didn't help.

Upvotes: 1

Views: 32

Answers (1)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 476709

With your annotation:

User.objects.annotate(
    b_count=Count('B_set', filter=(~Q(B_set__A__creator=F('user'))))
)

your F(..) attribute refers to a hypothetical User.user field, but a User has (probably) no user field.

If you want to refer to the "self" here, you can use F('pk') (or F('id'), given if id is the primary key), so you can write this expression as:

User.objects.annotate(
    b_count=Count('B_set', filter=~Q(B_set__a__model__creator=F('pk')))
)

This then results in a query like:

SELECT user.*,
       COUNT(
           CASE WHEN NOT a.creator_id = user.id AND a.creator_id IS NOT NULL
           THEN b.id ELSE NULL END
       ) AS b_count
FROM user
LEFT OUTER JOIN b ON user.id = b.user_id
LEFT OUTER JOIN a ON b.a_model_id=a.id
GROUP BY user.id

Upvotes: 1

Related Questions