python - django 查询未正确分组

Question

    class Category(BaseEntity):
        """ to categorise the expense """
        name = models.CharField(
            max_length=80,
            validators=[
                RegexValidator(
                    regex='^[a-zA-Z\s]*$',
                     message='name should contain only alphabets',
                    code='invalid_name'
                ),
            ]
        )
        description = models.CharField(
            max_length=250,
            validators=[
                RegexValidator(
                    regex='^[a-zA-Z\s]*$',
                     message='name should contain only alphabets',
                    code='invalid_name'
                ),
            ],
            null = True,
            blank = True
        )

        parent_category = models.ForeignKey(
                'self',
                related_name = 'child_categories',
                on_delete = models.CASCADE,
                null = True,
                blank = True
        )



class Expense(BaseEntity):
    """covers all the expenses"""

    SEGMENT_CHOICES=(
        ('RENTALS', 'Rentals'),
        ('MIXING', 'Mixing'),
        ('ALBUMS', 'Albums'),
        ('SALES', 'Sales'),
    )

    amount = models.IntegerField(
            default=500,
            validators=[
                MinValueValidator(
                    0,
                    message = 'Amount should be greater than 0'
                ),

                MaxValueValidator(
                    100000,
                    message = 'Amount should be less than 100000'
                ),
            ]
        )
    category = models.ForeignKey(
            'accounting.Category',
            related_name='expenses',
            on_delete=models.CASCADE,
            null = True,
            blank = True
    )

    date = models.DateField(
            verbose_name='spending date'
        )

    description = models.CharField(
        max_length=250,
        validators=[
            RegexValidator(
                regex='^[a-zA-Z\s]*$',
                 message='name should contain only alphabets',
                code='invalid_name'
            ),
        ],
        null = True,
        blank = True
    )
    event = models.ForeignKey(
            'booking.Event',
            related_name='event_expenses',
            blank = True,
            null =True,
            on_delete=models.CASCADE
        )
    business_segment= models.CharField(
        max_length =15,
        choices = SEGMENT_CHOICES,
        default = 'RENTALS',
    )

查询:

expense_category = Expense.objects.values('category__name').annotate(total=Sum('amount'))

输出如下:

 <QuerySet [{'category__name': 'Diesel', 'total': 2000}, {'category__name': 'Diesel', 'total': 2000}, {'category__name': 'Diesel', 'total': 2000}, {'category__name': 'Diesel', 'total': 3000}, {'category__name': 'web live', 'total': 1600}, {'category__name': 'Diesel', 'total': 2200}, {'category__name': 'Diesel', 'total': 1000}, {'category__name': 'Diesel', 'total': 1000}, {'category__name': 'Diesel', 'total': 500}, {'category__name': 'Diesel', 'total': 1600}, {'category__name': 'Diesel', 'total': 2500}, {'category__name': 'Bills', 'total': 200}, {'category__name': 'Diesel', 'total': 5600}]>

尽管 Diesel 有很多条目，但它没有正确分组。我在查询中遗漏了什么吗？我不喜欢为此进行列表处理，我希望通过查询方法进行处理。列表逻辑只会使事情变得复杂并浪费处理能力

Answer 1

注释Category对象(带有额外的属性)

首先，我认为从 Category 对象中查询更有意义，那么也许我们甚至不需要执行适当的分组依据，例如:

<b>Category</b>.objects.annotate(total=Sum(<b>'expenses__amount'</b>))

这将产生一个QuerySet，其中每个Category都有一个额外的属性.total。这可能会更好，因为这样您就可以访问 Category 的所有属性，并且它将表现得像 Category。

查询字典QuerySet

如果您只想要字典的 QuerySet，则需要在末尾添加 .order_by(..) 属性(是的，我知道这听起来很奇怪) ，例如:

qs = Category.objects.values(
    'name'
).annotate(
    total=Sum('expenses__amount')
)<b>.order_by('name')</b>

这将导致:

<QuerySet [
    { 'name': 'bar', 'total': 14.0 }
    { 'name': 'foo', 'total': 25.0 }
]>

但请注意，如果两个类别具有相同名称，它们将会相加，这可能并不理想。