Django Create Single Queryset That Counts Objects of Two Models By Month

Question

I have two models that I'm trying to get the object counts of each by month

class Car(models.Model):
    car_user = models.ForeignKey(User, on_delete=models.CASCADE)
    car_title = models.CharField(max_length=200)
    car_purchase_date = models.DateTimeField()

class Truck(models.Model):
    truck_user = models.ForeignKey(User, on_delete=models.CASCADE)
    truck_title = models.CharField(max_length=200)
    truck_purchase_date = models.DateTimeField()

Was able to get a count by User with this below, but can't figure out how to do the same thing but instead of by user doing by month. Won't bother wasting time and space with the dumb things I've tried so far. Really appreciate any help.

    count_by_user_chart = User.objects.values('username') \
        .annotate(car_count=Count('car__id', distinct=True)) \
        .annotate(truck_count=Count('truck__id', distinct=True)) \

End goal would be to be able to produce something like this -

+---------------+-----------+-------------+
|     Month     | Car Count | Truck Count |
+---------------+-----------+-------------+
| January 2023  |         3 |           1 |
| February 2023 |         4 |           0 |
| March 2023    |         0 |           2 |
| April 2023    |         2 |           2 |
+---------------+-----------+-------------+

Answer 1

Something as follows should work:

    # Get the current year and month
    now = datetime.now()
    year = now.year
    month = now.month

    # Get the counts of cars and trucks by month, (TruncMonth truncates DateTimeField or DateField to the month level)
    car_counts = Car.objects.annotate(month=TruncMonth('car_purchase_date')) \
        .filter(car_purchase_date__year=year, car_purchase_date__month__lte=month) \
        .values('month') \
        .annotate(count=Count('id'))

    truck_counts = Truck.objects.annotate(month=TruncMonth('truck_purchase_date')) \
        .filter(truck_purchase_date__year=year, truck_purchase_date__month__lte=month) \
        .values('month') \
        .annotate(count=Count('id'))

    # Create a dictionary to hold the counts by month
    counts_by_month = {}
    for i in range(1, 13):
        month_name_short = month_name[i][:3]
        counts_by_month[month_name_short] = {'car_count': 0, 'truck_count': 0}

    # Update the dictionary with the counts from the database
    for item in car_counts:
        month_name_short = item['month'].strftime("%b")
        counts_by_month[month_name_short]['car_count'] = item['count']

    for item in truck_counts:
        month_name_short = item['month'].strftime("%b")
        counts_by_month[month_name_short]['truck_count'] = item['count']

    rows = []
    for month_name_short in counts_by_month:
        car_count = counts_by_month[month_name_short]['car_count']
        truck_count = counts_by_month[month_name_short]['truck_count']
        rows.append({'month': month_name_short + " " + str(year),
                    'car_count': car_count, 'truck_count': truck_count})