#25861: Performing two conditional counts on foreign key fields
-------------------------------------+-------------------------------------
Reporter: luccascorrea | Owner: nobody
Type: Uncategorized | Status: closed
Component: Database layer | Version: 1.8
(models, ORM) | Resolution:
Severity: Normal | worksforme
Keywords: QuerySet.extra | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by timgraham):
* status: new => closed
* resolution: => worksforme
* component: Uncategorized => Database layer (models, ORM)
Old description:
> 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(model.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.
New description:
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.
--
Comment:
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/25861#comment:3>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/070.d6962f5f3d60d6bd8168a13be252d5ec%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
