How to query list while preserving order

Question

I wanted to do a query based on a list. I know I can do this:

lists = [Everton, Liverpool, Villa]
queryset = Betting.objects.filter(matches__in=list)

However, this doesnt query the table in order. The operation am doing is based on fuzzywuzzy and is vital the query is done in order of the list

I have seen this solution django-create-a-queryset-from-a-list-preserving-order.html but it doesnt seem to work with sqllite

Thanks..Any help will be appreciated

ORM table to be queried in order of the list..ie..It checks first for list[0], list[1] and the rest in order of how they appear in the list — Lawrence Muriuki, May 10 '16 at 18:17
Do you really need a queryset? If not you could just python sort after the query. — Shang Wang, May 10 '16 at 18:22
Yes I need a queryset, so as to be able to get other field attributes..eg book.date or book.author..To do this I need a queryset — Lawrence Muriuki, May 10 '16 at 18:26
Please refer to this answer: https://stackoverflow.com/questions/7361243/django-in-query-lookup-doesnt-maintain-the-order-in-queryset/70830205#70830205 — Rashid Mahmood, Jan 24 '22 at 07:23

wim · Answer 1 · 2016-05-10T19:40:00.783

3

Try something like this:

from django.db.models import Case, Value, When, IntegerField

cases = [When(matches=x, then=Value(i)) for i,x in enumerate(lists)] 
case = Case(*cases, output_field=IntegerField())
queryset = Betting.objects.filter(matches__in=lists)
queryset = queryset.annotate(my_order=case).order_by('my_order')

edited May 10 '16 at 19:40

answered May 10 '16 at 18:41

wim

338,267
99
616
750

Thanks ..but what is 'output_field=IntegerField()' – Lawrence Muriuki May 10 '16 at 19:05
`from django.db.models import IntegerField` , to store the output of the case check – wim May 10 '16 at 19:39

Shubham Aggarwal · Answer 2 · 2016-05-10T18:28:00.797

0

Not really sure of efficiency, but it can be a way

lists = [Everton, Liverpool, Villa]
queryset = Betting.objects.all()
for i in lists:
    queryset = queryset.filter(matches=i)

The reason you can do is that if you do, queryset.filter(some condition).filter(some other condition), then the result is actually the union of both filters in their order

edited May 10 '16 at 18:28

answered May 10 '16 at 18:17

Shubham Aggarwal

353
2
16

How to query list while preserving order

2 Answers2