[Answered ]-Using prefetch_related and aggregations to avoid n+1 issue with Django database queries for model with time series data

by

1👍

✅

The following should get you all suggestions, dates and sum of votes in a values queryset

from django.db.models import Max, Min
from django.db.models.functions import TruncDate


def index(request):
    suggestions = Suggestion.objects.annotate(
        date=TruncDate('vote__timestamp')
    ).order_by(
        'id', 'date'
    ).annotate(
        sum=Max('vote__votes') - Min('vote__votes')
    )
    return render(request, 'borgerforslag/index.html', {'suggestions': suggestions})

Then in template use regroup to group all those results by the suggestion

{% regroup suggestions by title as suggestions_grouped %}

<ul>
{% for suggestion in suggestions_grouped %}
    <li>{{ suggestion.grouper }}
    <ul>
        {% for date in suggestion.list %}
          <li>{{ date.date }}: {{ date.sum }}</li>
        {% endfor %}
    </ul>
    </li>
{% endfor %}
</ul>
👤Iain Shelvington

0👍

All you should need is values(), annotate(), and order_by() to get the number of votes per day per suggestion. This here should work

Vote.objects.all() \
    .values('timestamp__date', 'suggestion') \
    .annotate(num_votes=Count('votes') \
    .order_by('timestamp__date')

Although, your output example is not of number of votes per day per suggestion, and seems to be number of votes per day. That can be achieved by removing suggestion from the query like this:

Vote.objects.all() \
    .values('timestamp__date') \
    .annotate(num_votes=Count('votes') \
    .order_by('timestamp__date')
👤dichter

Leave a comment