Django创建一个按月计算两个模型对象的查询集

zed5wv10 于 2023-03-13 发布在 Go

关注(0)|答案(1)|浏览(116)

我有两个模型，我尝试按月获取每个模型的对象计数

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()

能够得到一个计数的用户与此下面，但无法找出如何做同样的事情，但不是由用户每月做。不会打扰浪费时间和空间与愚蠢的事情，我已经尝试了到目前为止。真的很感激任何帮助。

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

最终目标是能够生产出这样的东西-

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

django

来源：https://stackoverflow.com/questions/75717691/django-create-single-queryset-that-counts-objects-of-two-models-by-month

1条答案

按热度按时间

jdzmm42g1#

以下方法应该有效：

# 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})

赞(0）回复(0）举报 2023-03-13

我来回答

Django创建一个按月计算两个模型对象的查询集

1条答案

相关问题

热门标签

最新问答