Why using .last() on distincted queryset gives me object with was not primary in queryest?-CodePudding

Bid.objects.all() gives me two columns with date and bid_value. I want to have only one bid per date. Bid should be the highes value. So Bid.objects.all().order_by("date","bid_value").distinct("date") gives me proper order.

But using last not only takes last row from QuerySet, but it takes last element from group_by made from distinct which i dont want. I would want only last element of queryset without any order changes.

1.Bid.objects.all()

2.Bid.objects.all().order_by("date","bid").distinct("date")

3.Bid.objects.all().order_by("date","bid").distinct("date").last()

but it should give me 15.11.2022/50zł and actually

list(Bid.objects.all().order_by("date","bid").distinct("date").last())[-1]

gives me proper object

4.Bid.objects.all().order_by("date","bid").distinct("date").first()

How i can still use .last() / .first() - without taking it into list. Because when i use list(Queryset), i have proper values on last objects. But changing queryset to list is not good standard i suppose.

CodePudding user response：

What you want is a GROUP BY clause.

In Plain SQL you would write something like

SELECT bid_date, MAX(bid) 
FROM bids
GROUP BY bid_date 
ORDER BY bid_count DESC;

In Django you could use aggregations or annotations, but aggregate clauses are terminal whereas if you use annotations, it will return a QuerySet on which you can use the first() and last() calls.

Bid.objects \
.values('bid_date') \
.annotate(Max('bid_count')) \
.order_by('bid_count__max') \
.last()

This is a helpful article on group by queries in Django: https://simpleisbetterthancomplex.com/tutorial/2016/12/06/how-to-create-group-by-queries.html