Count by Month in Django Query

Question

Good afternoon,

I am struggling with a query. Ultimately, I have some records I want to query and get a count by month.

Ultimately, my goal is to come up with a data set that looks like:

Month, opened, closed
Jan, 2, 3
Feb, 1, 5

Etc... for the past six months.

I have been using this as a base: Django: Query Group By Month

This works to get each individually:

six_months_ago = datetime.now() - timedelta(days = 180)
open_six_months = (Rec.objects
   .annotate(open=Month('open_date'))
   .values('open')
   .annotate(rec_opened=Count('status', filter=Q(status__is_open=True)))
   .order_by())
        
closed_six_months = (Rec.objects
   .annotate(closed=Month('close_date'))
   .values('closed')
   .annotate(rec_closed=Count('status', filter=Q(status__is_open=False) and Q(close_date__isnull=False) ))
   .order_by())

Not all records have a closed date yet.

This returns the right data when testing, but its in two different queries.

I'd like to have one single data set if possible.

Is there an easy way to combine this into one query that has a single row with both the count of the open date and the count of the closed date by month?

Thank you for your help.

BCBB

Answer 1

An option might be to just join the two together as:

from django.db.models import IntegerField, Q, Value
from django.db.models.functions import ExtractMonth

six_months_ago = datetime.now() - timedelta(days=180)
all_items = (
    Rec.objects.values(
        open=ExtractMonth('open_date'),
        closed=Value(None, output_field=IntegerField()),
    )
    .annotate(item=Count('status', filter=Q(status__is_open=True)))
    .order_by()
    .union(
        Rec.objects.values(
            open=Value(None, output_field=IntegerField()),
            closed=ExtractMonth('close_date'),
        )
        .annotate(
            item=Count(
                'status',
                filter=Q(status__is_open=False, close_date__isnull=False),
            )
        )
        .order_by(),
        all=True,
    )
)

then we post-process as:

result = {}
for item in all_items:
    month = item['open'] or item['close']
    record = result.setdefault(month, [0, 0])
    record[item['open'] is None] += item['item']

This will map every month number on a list of two items with the first the number of items opened that month, and the second the number of items closed that month.