django order by key list

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 ;)

转载【http://blog.mathieu-leplatre.info/django-create-a-queryset-from-a-list-preserving-order.html】

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值