How can I filter by date to get this week vs previous week Counts from a QuerySet?

Question

I have the following two annotated QuerySets which I have generated separately by extracting this_wk and prev_wk from a data set, then performing the annotation. I would like to merge them together before returning them.

data = {
    'this_wk': QuerySet([{'this_total': 3, 'dept': 2}, 
                         {'this_total': 2, 'dept': 1}, 
                         {'this_total': 1, 'dept': 3}]),
    'prev_wk': QuerySet([{'prev_total': 2, 'dept': 3}])}

Expected:

QuerySet([{'this_total': 3, 'prev_total': None, 'dept': 2},
          {'this_total': 2, 'prev_total': None, 'dept': 1}, 
          {'this_total': 1, 'prev_total': 2, 'dept': 3}]),

Alternatively, is there a way of aggregating this directly so that I go straight from a set of items with date fields? Here's how I am currently generating the two QuerySets:

previous_items = Widget.objects.filter(start__gte=prev_start).filter(start__lte=prev_end)
prev_ranking = previous_items.values('dept').annotate(prev_total=Count('dept'))

this_items = Widget.objects.filter(start__gte=this_start
    ).filter(start__lte=this_end)
this_ranking = this_items.values('dept'
    ).annotate(this_total=Count('dept')
    ).order_by('-this_total')

Is there a way to chain these two annotations together so that I end up with a single QuerySet grouped and ranked for each Dept depending on how many Widgets it was used by this week, but also with an added field that says how many Widgets it was used by the previous week?

I'm thinking something like this:

items = Widget.objects.filter(start__gte=prev_start
    ).filter(start__lte=this_end
    ).annotate(prev_total=Count('dept')  # can I restrict this to the previous week?
    ).annotate(this_total=Count('dept')  # can I restrict this to the current week?
    ).order_by('-this_total')

score 2 · Answer 1 · answered May 19 '17 at 11:10

check out conditional aggregation:

https://docs.djangoproject.com/en/1.10/ref/models/conditional-expressions/#conditional-aggregation

items = (Widget.objects.filter(start__range=(prev_start, this_end))
         .values('dept')
         .annotate(prev_total=Count(Case(When(start__lte=prev_end, then='dept'), output_field=IntegerField())),
                   this_total=Count(Case(When(start__gte=this_start, then='dept'), output_field=IntegerField()))
                   )
         .order_by('-this_total'))

John Moutafis · Accepted Answer · 2017-09-28T07:54:51.007

1

What you are thinking seems correct for your case. In order to achieve what you need you can apply the following:

Small but helpful is the range lookup, which lets you filter between two values
To restrict your annotate arguments, you need to use conditional aggregation.

In the end your query will look like this:

items = Widget.objects.filter(start__range=(prev_start, this_end))
                      .values('dept')
                      .annotate(
                           prev_total=Count(
                               Case(
                                   When(start__lte=prev_end, then=1)),                 
                                   output_field=IntegerField())),
                           this_total=Count(
                               Case(
                                   When(start__gte=this_start, then=1), 
                                   output_field=IntegerField())))
                       .order_by('-this_total')

EDIT DUE TO COMMENT:

This step is not needed in this case, this would help to build an annotation to sum the values of the filtered dept fields.

I would utilize F() expression to take the value of each dept field inside the annotation, and Sum function instead of Count to add those values together.

edited Sep 28 '17 at 07:54

answered May 19 '17 at 11:44

John Moutafis

22,254
11
68
112

Thanks, this is definitely along the right lines. It's just missing `.values('dept')` before the first `annotate` to get exactly what I'm looking for. Also, why use the `F` expression and `Sum` rather than `Count`? Because of the race condition mentioned in the linked docs? – Jamie Bull May 19 '17 at 12:00
1

@JamieBull I was under the assumption that you needed to add the `dept` values together and have their sum. If this is not the case and you need just to count how many `dept` values there are, then I will edit the answer :). In the mean time, I will edit the `values('dept')` in the answer! – John Moutafis May 19 '17 at 12:06
Yes, it's just counting how many department values there are, so then I don't need the `F`? – Jamie Bull May 19 '17 at 12:11
@JamieBull In that case you do not need `F()`. I will edit my answer to match your case. – John Moutafis May 19 '17 at 12:13
@JamieBull I gave the answer 1 hour before John. Is there anything wrong with it? I am just curious if you got the correct results with it. – zaphod100.10 May 19 '17 at 13:29
1

All I saw was the "check out conditional aggregation" part and the link, so went off to read about it. By the time I came back there were two answers, one of which was much better explained and referenced the current docs. I only now realise that what we eventually arrived at is the same as your original answer, but with the addition of explanations which I think make it the better answer for anyone who arrives here in future. – Jamie Bull May 19 '17 at 13:34

How can I filter by date to get this week vs previous week Counts from a QuerySet?

2 Answers2