CODEWITHSUNDEEP
djangocountforeign-keysdjango-queryset
43Tesseracts
43Tesseracts

Reputation: 4937

Count of queryset where foreign key occurs exactly n times

If I have a django model with a foreign key, e.g:

class Article(models.Model):
    headline = models.CharField(max_length=100)
    pub_date = models.DateField()
    reporter = models.ForeignKey(Reporter, on_delete=models.CASCADE)

is there a way for me to get a count of the number of reporters that have exactly n articles on a specific date? For example, how many reporters have published exactly 2 articles "today"

date = timzone.now().date()
articles_on_date = Article.objects.filter(pub_date=date)
# now what can I do?

Edit: Currently I can only figure out how to do it very inneficiently by looping and hitting the database way to many times.

Upvotes: 0

Views: 161

Answers (2)

Ivan
Ivan

Reputation: 2675

Using conditional expressions:

from django.db import models

Reporter.objects.annotate(
    num_of_articles=models.Count(
        models.Case(models.When(article__pub_date=date, then=1), output_field=models.IntegerField())
    )
).filter(num_of_articles=2).count()

Upvotes: 3

JPG
JPG

Reputation: 88519

Try this,

from django.db.models import Count

Article.objects.filter(pub_date=date).values('reporter').annotate(article_count=Count('id')).filter(article_count=2)


This would return a list as below,
[{'reporter': 1, 'article_count': 2}]
The 1 corresponds to reporter is the id of the reporter instance

Upvotes: 1

Related Questions