[Answer]-Django Query: Annotate with `count` of a *window*

by

1👍

✅

Checkout out the docs on the order of annotate and filter: https://docs.djangoproject.com/en/1.5/topics/db/aggregation/#order-of-annotate-and-filter-clauses

Try something along the lines of:

activity_per_source = Source.objects.\
    filter(data__date__gte=one_week_ago).\
    annotate(count_data_records=Count('Data')).\
    order_by('-count_data_records').distinct()
👤Robert Jørgensgaard Engdahl

0👍

There is a way of doing that mixing Django queries with SQL via extra:

start_date = datetime.date.today() - 7
activity_per_source = (
    Source.objects
    .extra(where=["(select max(date) from app_data where source_id=app_source.id) >= '%s'"
           % start_date.strftime('%Y-%m-%d')])
    .annotate(count_data_records=Count('Data'))
    .order_by('-count_data_records'))

The where part will filter the Sources by its Data last date.

Note: replace table and field names with actual ones.

👤augustomen

Leave a comment