CODEWITHSUNDEEP
pythondjango
XelharK
XelharK

Reputation: 609

Django 1.8 filtered aggregation

I have two different models for different type of articles

class Topic(models.Model):
    name = models.CharField()

class Article(models.Model):
    published = models.BooleanField()
    topics = models.ManyToManyField('topic', blank=True, related_name="related_%(app_label)s_%(class)s")
    # Other stuff

class Insight(Article):
    related_news = models.ManyToManyField('News')
    related_insights = models.ManyToManyField(self)
    related_projects = models.ManyToManyField('Project')

class News(Article):
    related_news = models.ManyToManyField(self)
    related_insights = models.ManyToManyField('Insight')
    related_projects = models.ManyToManyField('Project')

class Project(models.Model):
    published = models.BooleanField()
    topic = models.ManyToManyField(Topic, related_name='projects')
    related_projects = models.ManyToManyField(self)
    # Other stuff

I would like to obtain a list of the topics with the number of News / Insights / Projects that have an association with them.

This is my query:

Industry.objects.all() \
    .annotate(
        projects_count=Sum(Case(
            When(projects__project__published=True,
                 projects__project__archived=False,
                 then=1),
            default=0,
            output_field=IntegerField()
        )),
        news_count=Sum(Case(
            When(related_articles_news__published=True, then=1),
            default=0,
            output_field=IntegerField()
        )),
        insights_count=Sum(Case(
            When(related_articles_insight__publish=True, then=1),
            default=0,
            output_field=IntegerField()
        ))

I got very weird results. If I only include "projects_count" then everything works fine. If I include "projects_count" and "news_count" everything works as well.

If I include "insights_count" alone, it works again, very well, but as soon as I include both projects and insights together, the annotations get doubled or tripled or even more.

I looked at the sql, and I noticed that when the results are weird it happens because the sql joins the related projects as well, and this screws up the final results.

Am I missing something obvious?

I'm using Django 1.8.2.

Upvotes: 0

Views: 29

Answers (1)

Mark Lavin
Mark Lavin

Reputation: 25164

This sounds like a bug I encountered (and fixed) https://code.djangoproject.com/ticket/24924. This has landed on the stable/1.8.x branch but at the time of this answer is waiting on a 1.8.3 release. You could try installing Django from the branch in the meantime.

$ pip install git+https://github.com/django/django@stable/1.8.x

Upvotes: 3

Related Questions