Django Queryset Postgres

Question

I am writing queryset that will return this type

date	total_shipping_fee
2021-04-16	5,000
2021-04-17	100,000

where

class Payments(models.Model):
    created = models.DateTimeField()
    .....

SELECT DATE(created) from payment_payment .... group by 'created' -> outputs a correct query

My question is how to query/ or cast

Payment.objects.filter(created_range=("2021-05-14", "2021-05-14")).values('created').annotate(total_shipping_fee=Sum('total_shipping_fee'))

so that I can have queryset in above raw sql. I think that is my problem to CAST DATE(created) in django queryset. Thanks

willeM_ Van Onsem · Accepted Answer · 2021-08-21 12:46:02Z

1

You can work with:

from django.db.models import F, Sum

Payment.objects.filter(
    created__date_range=('2021-05-14', '2021-05-14')
).values(create_date=F('created__date')).annotate(
    total_shipping_fee=Sum('total_shipping_fee')
).order_by('create_date')

here we thus first takes as values the truncated date of the DateTimeField, and then we use .order_by(…) [Django-doc] to enforce grouping by that date.

The output is a QuerySet of dictionaries, each with two items: { 'create_date': …, 'total_shipping_fee': … }.

answered Aug 21, 2021 at 12:46

willeM_ Van Onsem

482k33 gold badges483 silver badges624 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Django Queryset Postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related