[Chartjs]-How to make a complex query? | ChartJS and Django

by

2👍

✅

If I understand right, you want to count objects of type appeal in the current year, per month, with those related to an appeal_form form_name="written" separate from appeal_form__form_name="oral"?

What I know works, counting by month. I’ve tested this with a model of my own called PO (purchase order) with field date just like your Appeal.

>>> qs1 = PO.objects.filter( date__year=2023)
>>> result = qs1.aggregate( **{ 
        f'{x+1:02}' : Count('date', filter=Q(date__month=x+1) )
        for x in range(12) })
>>> result
{'01': 6, '02': 3, '03': 3, '04': 3, '05': 1, '06': 0, '07': 0, '08': 0, '09': 0, '10': 0, '11': 0, '12': 0}

>>> [ x[1] for x in sorted( result.items())]
[6, 3, 3, 3, 1, 0, 0, 0, 0, 0, 0, 0]

A simple result.values() gives the same list but that’s taking a lot on trust with respect to the ordering of queryset results. Better to explicitly sort them as you require.

Also it was a typo not to put f'M{x+1:02}' so the key starts with an alphabetic character. But, it worked! Who knew Python allowed that?

Now, with your one you could run two similar queries:

qs1 = Appeal.objects.filter( date__year=2023, appeal_form__form_name='oral')  
oral_results = qs1.aggregate( **{ ... 
qs2 = Appeal.objects.filter( date__year=2023, appeal_form__form_name='written')  
written_results = qs2.aggregate( **{ ...

Or you could put the criterion into the Q objects (24 of them) and do it all with one query and disentangle them afterwards. The code won’t be nearly as clear, and longer.

I’m not a DB expert and I’ve no idea whether this is efficient. For my purposes, I’m not working on enough DB rows for PostgreSQL to even blink, and these reports get done once per month!

Incidentally it’s quite easy to do last 12 months with this technique

>>> from datetime import date

>>> qs3 = PO.objects.filter( date__range=(date(2022,6,1), date(2023,5,31)) )   
>>> months = [(2022,x) for x in range(6,13)] + [ (2023,x) for x in range(1,6)]
>>> months
[(2022, 6), (2022, 7), (2022, 8), (2022, 9), (2022, 10), (2022, 11), (2022, 12), (2023, 1), (2023, 2), (2023, 3), (2023, 4), (2023, 5)]

 >>> qs3.aggregate( **{ f'{x[0]}_{x[1]:02}' : Count('date', filter=Q(date__month=x[1]) ) for x in months })
{'2022_06': 2, '2022_07': 5, '2022_08': 0, '2022_09': 6, '2022_10': 4, '2022_11': 1, '2022_12': 3, '2023_01': 6, '2023_02': 3, '2023_03': 3, '2023_04': 3, '2023_05': 1}

Leave a comment