Queryset排序：指定django ORM查询的列排序规则 [英] Queryset sorting: Specifying column collation for django ORM query

问题描述

我开始调查为什么我的Django Model.objects.filter（condition = variable）.order_by（textcolumn）查询不正确地输出对象。并发现它是数据库（Postgresql）问题。

I started investigating why my Django Model.objects.filter(condition = variable).order_by(textcolumn) queries do not yield objects in correct order. And found out that it is database (Postgresql) issue.

在我之前的问题（ Postgresql排序语言特定字符（排序规则））我想出了（从 zero323 实际上让它工作），我可以指定每个数据库查询的排序规则，如下所示：

In my earlier question (Postgresql sorting language specific characters (collation)) i figured out (with a lot of help from zero323 in actually getting it to work) that i can specify collation per database query like this:

SELECT nimi COLLATE "et_EE" FROM test ORDER BY nimi ASC;

但是只要我看到，order_by只接受字段名称作为参数。

But as much as i can see, order_by only accepts field names as arguments.

我想知道，如果以某种方式扩展该功能以包括排序规则参数？有可能以某种方式使用mixins或者什么来破解它吗？或者是功能要求现在唯一的方法吗？

I was wondering, that if it is somehow possible to extend that functionality to include also the collation parameter? Is it possible to hack it in somehow using mixins or whatnot? Or is feature request the only way to do this right now?

我希望它可以这样工作：

I wish it would work something like this:

Model.objects.filter(condition = variable).order_by(*fieldnames, collation = 'et_EE')

Edit1：
显然不是唯一一个要求：
https://groups.google.com/forum/#!msg/django-developers/0iESVnawNAY/JefMfAm7nQMJ

Alan

推荐答案

由于Django 1.8 order_by（）不仅接受字段名称而且查询表达式。

在另一个答案我给了一个例子，说明如何覆盖列的默认排序规则。这里的有用的查询表达式是 Func（） ，您可以直接使用或使用：

In another answer I gave an example of how you can override the default collation for a column. The useful query expression here is Func(), which you may subclass or use directly:

nimi_et = Func(
    'nimi',
    function='et_EE',
    template='(%(expressions)s) COLLATE "%(function)s"')
Test.objects.order_by(nimi_et.asc())

然而，请注意，生成的SQL将更像：

Yet, note that the resulting SQL will be more like:

SELECT nimi FROM test ORDER BY nimi COLLATE "et_EE" ASC;

也就是说，排序规则在 ORDER BY 子句而不是 SELECT 子句。但是，如果您需要在 WHERE 子句中使用它，您可以在<$ c $中使用 Func（） c> annotate（）。

That is, the collation is overridden in ORDER BY clause rather than in SELECT clause. However, if you need to use it in a WHERE clause, you can use Func() in annotate().

这篇关于Queryset排序：指定django ORM查询的列排序规则的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！