20

Django : Create a QuerySet from a list, preserving order

 3 years ago
source link: https://blog.mathieu-leplatre.info/django-create-a-queryset-from-a-list-preserving-order.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client
Django : Create a QuerySet from a list, preserving order

Django : Create a QuerySet from a list, preserving order

Fri 08 November 2013

I thought it would be an easy one, but found myself lost with 34 opened tabs on stackoverflow...

The problem : keep it ordered

Usually, obtaining a QuerySet from a list is quite simple :

>>> queryset = Theme.objects.filter(pk__in=[1, 2, 10])
>>> type(queryset)
<class 'django.db.models.query.QuerySet'>
>>> queryset
[<Theme: Fauna>, <Theme: Flora>, <Theme: Refuge>]

The problem is that the list order is ignored :

>>> Theme.objects.filter(pk__in=[10, 2, 1])
[<Theme: Fauna>, <Theme: Flora>, <Theme: Refuge>]

If obtaining a QuerySet is not a requirement, it's rather easy to get a list sorted according to another :

pks_list = [10, 2, 1]
themes = list(Theme.objects.filter(pk__in=pks_list))
themes.sort(key=lambda t: pks_list.index(t.pk))

In my case, I want a QuerySet, a brave lazy one, with proper filter(), exclude(), values() ...

Fallback to SQL

AFAIK, most database engines ignore order of records, until you specify an ordering column. In our case, the list is arbitrary, and does not map to any existing attribute, thus db column.

If you use MySQL (who does?!), there is a FIELD() function that provides custom input for the sort method :

SELECT *
FROM theme
ORDER BY FIELD(`id`, 10, 2, 1);

Using the ORM, it gives us (thanks Daniel Roseman)

pk_list = [10, 2, 1]
ordering = 'FIELD(`id`, %s)' % ','.join(str(id) for id in pk_list)
queryset = Theme.objects.filter(pk__in=[pk_list]).extra(
           select={'ordering': ordering}, order_by=('ordering',))

Well, good news it can be ported to PostgreSQL. But if possible, I would prefer native SQL.

And it looks like the magnificient syntax of SQL provides ORDER BY CASE WHEN ... END !

SELECT *
FROM theme
ORDER BY
  CASE
    WHEN id=10 THEN 0
    WHEN id=2 THEN 1
    WHEN id=1 THEN 2
  END;

Using the ORM, it gives us :

pk_list = [10, 2, 1]
clauses = ' '.join(['WHEN id=%s THEN %s' % (pk, i) for i, pk in enumerate(pk_list)])
ordering = 'CASE %s END' % clauses
queryset = Theme.objects.filter(pk__in=pk_list).extra(
           select={'ordering': ordering}, order_by=('ordering',))

I wonder how it behaves with zillions of records though ;)

One more thing: before Django 1.6, there was a bug with calling values_list() on a queryset ordered by an extra column. Use this :

values = queryset.values('ordering', 'label')
labels = [value['label'] for value in values]

Good luck ! Please share your advices or critics ;)

#django - Posted in the Dev category

© Copyright 2020 by Mathieu Leplatre. mnmlist Theme

Content licensed under the Creative Commons attribution-noncommercial-sharealike License.


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK