Opened 4 years ago

Closed 4 years ago

Last modified 4 years ago

#32685 closed New feature (wontfix)

Add feature to preserve order in .filter(field__in=list) query

Reported by: Barney Szabolcs Owned by: nobody
Component: Database layer (models, ORM) Version: 2.2
Severity: Normal Keywords:
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by Barney Szabolcs)

filter __in query does not guarantee to preserve order, so I usually sort afterwards using python's sort.

However, in django admin I cannot sort queries using python's sort when manipulating the QuerySet
since I have to return a QuerySet.
So, I'd do something like

queryset.filter(my_field__in=my_values).raw(
    f'order by array_position(ARRAY[{ ",".join(["%s"]*len(my_values)) }]::varchar[], my_field)', 
    params=my_values)

but this does not work...
is there a solution here? (I cannot use raw either since I have a queryset input argument to work with)

UPDATE:

now, I've found a cryptic solution:
https://stackoverflow.com/a/37648265/1031191

from django.db.models import Case, When

preserved = Case(*[When(my_field=val, then=pos) for pos, val in enumerate(my_values)], default=len(my_values))
queryset.filter(my_field__in=my_values).order_by(preserved)

I think Django should provide a better way than this.

Maybe queryset.filter(my_field__in_preserve=my_values)

Change History (3)

comment:1 by Barney Szabolcs, 4 years ago

Description: modified (diff)

comment:2 by Mariusz Felisiak, 4 years ago

Component: UncategorizedDatabase layer (models, ORM)
Resolution: wontfix
Status: newclosed
Type: UncategorizedNew feature

This behavior depends on a database, it has nothing to do with Django itself. If you want to preserve the ordering from the IN clause you can use Case or split your queryset into multiple combined queries. IMO neither of these workarounds should be built-in into Django, they are a bit hacky and quite niche.

comment:3 by Barney Szabolcs, 4 years ago

Description: modified (diff)
Note: See TracTickets for help on using tickets.
Back to Top