Django ORM queryset, include index in values_list


I have a queryset looks like this.
toys = Toy.objects.filter(…).order_by(‘price’, ‘id’).values_list(‘id’, ‘price’, ‘name’)

So the tuples inside the queryset look like this
(1, 35, ‘Woody’), (2, 45, ‘E-car’)

I want to add another value which is actually order of sorted result like this.
(1, 35, ‘Woody’, 0), (2, 45, ‘E-car’, 1), (13, 56, ‘G-toy’, 2)

How can I do this in queryset?

Thanks in advance

Django has the RowNumber function which can be used to annotate a result set. It’s a window function making it a little “fiddly” to use (my opinion), but there are samples floating around that might point you in the right direction. (I think it’s going to be something along the lines of:
Toy.objects.filter(...).annotate(row_number=Window(expression=RowNumber(), order_by=F('price'),)).order_by('price')

You can also augment your result set when you iterate over those results if you don’t want to do it in the queryset.

1 Like

Hi, @KenWhitesell

Thank you for your answer.
It works, that’s exactly what I wanted.