Opened 9 years ago

Closed 9 years ago

#25861 closed Uncategorized (worksforme)

Performing two conditional counts on foreign key fields — at Version 3

Reported by: luccascorrea Owned by: nobody
Component: Database layer (models, ORM) Version: 1.8
Severity: Normal Keywords: QuerySet.extra
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by Tim Graham)

I am just posting this because the documentation advises to file a ticket whenever it is not possible to not use a queryset's "extra" method when performing a query.

I was not able to perform the following query using only the ORM and not relying on the "extra" method:

class Article(models.Model):
    title = models.CharField(max_length=100, null=True)
    body = models.TextField(null=True)
    pubDate = models.DateTimeField()

class ArticleLike(models.Model):
    article = models.ForeignKey('article.Article')
    author = models.ForeignKey('accounts.User')

class ArticleView(models.Model):
    article = models.ForeignKey('article.Article')
    author = models.ForeignKey('accounts.User')


articles = Article.objects.extra(select={
    "likesCount": "SELECT COUNT(DISTINCT `article_articlelike`.`id`) FROM `article_articlelike` WHERE `article_articlelike`.`date` >= `article_article`.`pubDate` AND `article_articlelike`.`article_id` = `article_article`.`id`",
        "viewsCount": "SELECT COUNT(DISTINCT `article_articleview`.`id`) FROM `article_articleview` WHERE `article_articleview`.`date` >= `article_article`.`pubDate` AND `article_articleview`.`article_id` = `article_article`.`id`"

To make it clearer, the question this query is trying to answer is: how many likes and views did each article receive after it was published?

It seems kind of odd that an article would be liked and viewed before being published but the thing is that the article could be republished and in this situation its pubDate would be updated, and so it should not take into account the likes/views received before being republished, however these likes/views cannot be deleted as to maintain a history of likes/views.

So the problem is simple, two simultaneous conditional counts, where the condition is in two foreign keys.

I was not able to use "annotate(likesCount=..., viewsCount=..)" because the "count" ends up multiplying both annotations.

Change History (4)

comment:1 by luccascorrea, 9 years ago

comment:2 by Josh Smeaton, 9 years ago

Does this query work correctly?

from django.db.models import Count
from django.db.models.expressions import Case, When

likes = Q()

Article.objects.annotate(
    likes=Count(
        Case(When(articlelike__date__gte=F('pubdate'), then=1, else=0)),
        distinct=True
    ),
    views=Count(
        Case(When(articleview__date__gte=F('pubdate'), then=1, else=0)),
        distinct=True
    )
)

I'm guessing this will result in duplicates also, but can you give it a try anyway?

The ORM doesn't do aggregation across multiple reverse relations (or m2m relations) very well. I'm not too certain about the reason unfortunately. Anssi would know better than I, but I suspect it has something to do with not creating subqueries.

by Tim Graham, 9 years ago

Attachment: t25861.tar.gz added

comment:3 by Tim Graham, 9 years ago

Component: UncategorizedDatabase layer (models, ORM)
Description: modified (diff)
Resolution: worksforme
Status: newclosed

This query seems to work for me:

Article.objects.annotate(
    likes=Count(
        Case(When(articlelike__date__gte=F('pubDate'), then=F('articlelike__id'))),
        distinct=True
    ),
    views=Count(
        Case(When(articleview__date__gte=F('pubDate'), then=F('articleview__id'))),
        distinct=True
    )
)

I've attached the sample project I used for testing.

Note: See TracTickets for help on using tickets.
Back to Top