Using Django ORM, can one do something like queryset.objects.annotate(Count('queryset_objects', gte=VALUE))
. Catch my drift?
Here's a quick example to use for illustrating a possible answer:
In a Django website, content creators submit articles, and regular users view (i.e. read) the said articles. Articles can either be published (i.e. available for all to read), or in draft mode. The models depicting these requirements are:
class Article(models.Model): author = models.ForeignKey(User) published = models.BooleanField(default=False) class Readership(models.Model): reader = models.ForeignKey(User) which_article = models.ForeignKey(Article) what_time = models.DateTimeField(auto_now_add=True)
My question is: How can I get all published articles, sorted by unique readership from the last 30 mins? I.e. I want to count how many distinct (unique) views each published article got in the last half an hour, and then produce a list of articles sorted by these distinct views.
I tried:
date = datetime.now()-timedelta(minutes=30) articles = Article.objects.filter(published=True).extra(select = { "views" : """ SELECT COUNT(*) FROM myapp_readership JOIN myapp_article on myapp_readership.which_article_id = myapp_article.id WHERE myapp_readership.reader_id = myapp_user.id AND myapp_readership.what_time > %s """ % date, }).order_by("-views")
This sprang the error: syntax error at or near "01" (where "01" was the datetime object inside extra). It's not much to go on.
Use Django's count() QuerySet method — simply append count() to the end of the appropriate QuerySet. Generate an aggregate over the QuerySet — Aggregation is when you "retrieve values that are derived by summarizing or aggregating a collection of objects." Ref: Django Aggregation Documentation.
A QuerySet is evaluated when you call len() and it saves evaluated results to cache. # len() evaluates and saves results to cache.
Aggregate calculates values for the entire queryset. Annotate calculates summary values for each item in the queryset.
Use Conditional Aggregation:
from django.db.models import Count, Case, When, IntegerField Article.objects.annotate( numviews=Count(Case( When(readership__what_time__lt=treshold, then=1), output_field=IntegerField(), )) )
Explanation: normal query through your articles will be annotated with numviews
field. That field will be constructed as a CASE/WHEN expression, wrapped by Count, that will return 1 for readership matching criteria and NULL
for readership not matching criteria. Count will ignore nulls and count only values.
You will get zeros on articles that haven't been viewed recently and you can use that numviews
field for sorting and filtering.
Query behind this for PostgreSQL will be:
SELECT "app_article"."id", "app_article"."author", "app_article"."published", COUNT( CASE WHEN "app_readership"."what_time" < 2015-11-18 11:04:00.000000+01:00 THEN 1 ELSE NULL END ) as "numviews" FROM "app_article" LEFT OUTER JOIN "app_readership" ON ("app_article"."id" = "app_readership"."which_article_id") GROUP BY "app_article"."id", "app_article"."author", "app_article"."published"
If we want to track only unique queries, we can add distinction into Count
, and make our When
clause to return value, we want to distinct on.
from django.db.models import Count, Case, When, CharField, F Article.objects.annotate( numviews=Count(Case( When(readership__what_time__lt=treshold, then=F('readership__reader')), # it can be also `readership__reader_id`, it doesn't matter output_field=CharField(), ), distinct=True) )
That will produce:
SELECT "app_article"."id", "app_article"."author", "app_article"."published", COUNT( DISTINCT CASE WHEN "app_readership"."what_time" < 2015-11-18 11:04:00.000000+01:00 THEN "app_readership"."reader_id" ELSE NULL END ) as "numviews" FROM "app_article" LEFT OUTER JOIN "app_readership" ON ("app_article"."id" = "app_readership"."which_article_id") GROUP BY "app_article"."id", "app_article"."author", "app_article"."published"
You can just use raw
for executing SQL statement created by newer versions of django. Apparently there is no simple and optimized method for querying that data without using raw
(even with extra
there are some problems with injecting required JOIN
clause).
Articles.objects.raw('SELECT' ' "app_article"."id",' ' "app_article"."author",' ' "app_article"."published",' ' COUNT(' ' DISTINCT CASE WHEN "app_readership"."what_time" < 2015-11-18 11:04:00.000000+01:00 THEN "app_readership"."reader_id"' ' ELSE NULL END' ' ) as "numviews"' 'FROM "app_article" LEFT OUTER JOIN "app_readership"' ' ON ("app_article"."id" = "app_readership"."which_article_id")' 'GROUP BY "app_article"."id", "app_article"."author", "app_article"."published"')
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With