[Fixed]-Creating Django pgSQL Query With COUNT , GROUP BY , INTERVAL and LIMIT

by

2👍

✅

You can use a .filter() to limit to those changed in the last 60 days. It is possible to use a database function NOW(), or you could just do that math in python:

.filter(access_date__gt=datetime.datetime.utcnow() - datetime.timedelta(days=60))

The ordering is likewise possible:

.order_by('-total')

And finally, you can take a slice on the queryset to get just the first 10 results:

[:10]

So, your final query might look something like:

result = UsageStatistics.objects.filter(
    access_date__gte=datetime.datetime.utcnow() - datetime.timedelta(days=60)
).values('content_reference').annotate(
    total=Count('reference')
).order_by('-total')[:10]
👤Matthew Schinckel

-1👍

using:
result =UsageStatistics.objects.raw(“SELECT content_reference , COUNT(reference)
AS total
FROM usage_statistics
WHERE content_type = ‘blog’
AND access_date > NOW() – INTERVAL ’90’ DAY
GROUP BY content_reference
ORDER BY total DESC
LIMIT 10 “)

check https://docs.djangoproject.com/en/dev/topics/db/sql/ for more details.

👤zhiqiang huang

Leave a comment