Django ORM Group By ID

Question

I am trying to get some information from my table; total price, total trip etc between two dates. Here is my query

start_date = Utils.subtract_day(datetime.datetime.today(), 30)
end_date = datetime.datetime.today()
trips = TbPastTrips.objects.filter(identity=identity, creation_time__range=(start_date, end_date),
                                   status=TripStatus.PAYMENT_COMPLETED)\
    .annotate(total_price__=Sum('price'), total_tip=Sum('tip_amount'),
              total_additional_fees=Sum('additional_fees'), total_trip=Count('price')) \
    .values('total_price__', 'total_tip', 'total_additional_fees', 'total_trip')

print(trips.query)

This is the raw sql query that I expect from above queryset:

SELECT SUM(`tb_past_trips`.`PRICE`) AS `total_price__`, SUM(`tb_past_trips`.`TIP_AMOUNT`) AS `total_tip`, SUM(`tb_past_trips`.`ADDITIONAL_FEES`) AS `total_additional_fees`, COUNT(`tb_past_trips`.`PRICE`) AS `total_trip` FROM `tb_past_trips` WHERE (`tb_past_trips`.`IDENTITY` = 44444444444 AND `tb_past_trips`.`CREATION_TIME` BETWEEN '2017-08-29 10:36:19.446371' AND '2017-09-28 10:36:19.446405' AND `tb_past_trips`.`STATUS` = 4)

But it adds below extra query:

GROUP BY tb_past_trips.ID ORDER BY NULL

to end of the query. But I don't want to group it.

So what is the wrong thing that i am doing?

Django version :(1, 11, 5, 'final', 0)

Python 3.6.2

Added Model:

class TbPastTrips(models.Model):
    id = models.AutoField(db_column='ID', primary_key=True)  # Field name made lowercase.
    transaction_id = models.CharField(db_column='TRANSACTION_ID', max_length=50, blank=True, null=True)  # Field name made lowercase.
    identity = models.CharField(db_column='TAXI', max_length=20)  # Field name made lowercase.
    price = models.IntegerField(db_column='PRICE', blank=True, null=True)  # Field name made lowercase.
    tip_amount = models.IntegerField(db_column='TIP_AMOUNT', blank=True, null=True)  # Field name made lowercase.
    additional_fees = models.IntegerField(db_column='ADDITIONAL_FEES', blank=True, null=True)  # Field name made lowercase.
    total_price = models.IntegerField(db_column='TOTAL_PRICE', blank=True, null=True)  # Field name made lowercase.
    creation_time = models.DateTimeField(db_column='CREATION_TIME')  # Field name made lowercase.

    class Meta:
        managed = False
        db_table = 'tb_past_trips'

what is your model? how did you manged it? is it database view? — Brown Bear, Sep 28 '17 at 11:22
I found it, missusage, i must use aggregation not annotate. https://stackoverflow.com/questions/7981837/difference-between-djangos-annotate-and-aggregate-methods — RockOnGom, Sep 28 '17 at 11:23
good if it help you, any way you can add answer on your question it can help others — Brown Bear, Sep 28 '17 at 11:26

score 2 · Answer 1 · answered Sep 28 '17 at 11:26

Use aggregation not annotate,

Per-object summaries can be generated using the annotate() clause. When an annotate() clause is specified, each object in the QuerySet will be annotated with the specified values.

Aggregation

What we need is a way to calculate summary values over the objects that belong to this QuerySet. This is done by appending an aggregate() clause onto the QuerySet

Brown Bear · Answer 2 · 2017-09-28T10:56:02.253

1

you just need added empty order by .order_by() more details default-ordering-or-order-by

edited Sep 28 '17 at 10:56

answered Sep 28 '17 at 10:53

Brown Bear

19,655
10
58
76

added your models please, i try it – Brown Bear Sep 28 '17 at 10:57

Django ORM Group By ID

2 Answers2