Django：自然排序查询集

Question

我正在寻找一种自然排序 Django 的 QuerySets 的方法。我找到了一个类似的问题，但它并没有关注 QuerySets。相反，他们是直接在 Python 中执行。

所以这就是我的问题。假设我有以下模型：

class Item(models.Model):
    signature = models.CharField('Signatur', max_length=50)

在 Django 管理界面中，我想使用一个过滤器，按字母数字排序。当前它们是这样排序的： 我期望得到的是一个类似于["BA 1", "BA 2", ...]的列表。在官方文档中找到了一个名为 admin.SimpleListFilter 的工具，听起来很适合。但在 queryset() 函数中得到的是一个 QuerySet ，无法进行自然排序，因为它不包含元素，只包含对数据库的查询。QuerySet 上的 order_by 方法给出了与图像中相同的排序。有没有办法操纵 QuerySet 以获得自然排序？ 我迄今为止的代码：

class AlphanumericSignatureFilter(admin.SimpleListFilter):
    title = 'Signature (alphanumeric)'
    parameter_name = 'signature_alphanumeric'

    def lookups(self, request, model_admin):
        return (
            ('signature', 'Signature (alphanumeric)'),
        )

    def queryset(self, request, queryset: QuerySet):
        return queryset.order_by('signature')

如何将查询集转换为我想要的输出？或者有没有其他方法？Django管理界面非常强大，这就是为什么我希望能够尽可能地使用它。但是这个功能真的很缺失。

我目前正在使用Django 1.11

任何帮助、评论或提示都会受到赞赏。感谢您的帮助。

- n2o

Answer 1

其实那不是Django的错误，那是数据库内部工作的方式，例如 MySql 默认情况下没有自然排序（我没有搜索太多，所以可能我错了）。但我们可以使用一些解决方法来处理这种情况。

我将所有内容都放在了https://gist.github.com/phpdude/8a45e1bd2943fa806aeffee94877680a，其中包括示例和截图。

但基本上对于给定的 models.py 文件。

from django.db import models


class Item(models.Model):
    signature = models.CharField('Signatur', max_length=50)

    def __str__(self):
        return self.signature

我只是以正确的筛选器实现为例，使用了 admin.py

from django.contrib.admin import ModelAdmin, register, SimpleListFilter
from django.db.models.functions import Length, StrIndex, Substr, NullIf, Coalesce
from django.db.models import Value as V

from .models import Item


class AlphanumericSignatureFilter(SimpleListFilter):
    title = 'Signature (alphanumeric)'
    parameter_name = 'signature_alphanumeric'

    def lookups(self, request, model_admin):
        return (
            ('signature', 'Signature (alphanumeric)'),
        )

    def queryset(self, request, queryset):
        if self.value() == 'signature':
            return queryset.order_by(
                Coalesce(Substr('signature', V(0), NullIf(StrIndex('signature', V(' ')), V(0))), 'signature'),
                Length('signature'),
                'signature'
            )


@register(Item)
class Item(ModelAdmin):
    list_filter = [AlphanumericSignatureFilter]

示例屏幕截图：

一些参考资料:

• http://www.mysqltutorial.org/mysql-natural-sorting/
• https://docs.djangoproject.com/en/2.0/ref/contrib/admin/

注：看起来Django 1.9添加了db函数Length(column_name)，所以您应该能够使用它，但通常任何Django版本都支持自定义db ORM函数调用，并且可以调用字段的length()函数。

---

使用Python库natsort的额外示例

它可以工作，但需要在正确排序之前加载所有可能的签名，因为它是在Python端而不是在数据库端对行列表进行排序。

它可以工作。但是如果表格大小很大，则可能会非常慢。

从我的角度来看，它应该仅在数据库表大小小于50,000行时使用（例如，取决于您的DB服务器性能等）。

from django.contrib.admin import ModelAdmin, register, SimpleListFilter
from django.db.models.functions import StrIndex, Concat
from django.db.models import Value as V
from natsort import natsorted

from .models import Item


class AlphanumericTruePythonSignatureFilter(SimpleListFilter):
    title = 'Signature (alphanumeric true python)'
    parameter_name = 'signature_alphanumeric_python'

    def lookups(self, request, model_admin):
        return (
            ('signature', 'Signature (alphanumeric)'),
        )

    def queryset(self, request, queryset):
        if self.value() == 'signature':
            all_ids = list(queryset.values_list('signature', flat=True))
            # let's use "!:!" as a separator for signature values
            all_ids_sorted = "!:!" + "!:!".join(natsorted(all_ids))

            return queryset.order_by(
                StrIndex(V(all_ids_sorted), Concat(V('!:!'), 'signature')),
            )


@register(Item)
class Item(ModelAdmin):
    list_filter = [AlphanumericTruePythonSignatureFilter]

以下是另一个截图示例，用于展示Python侧使用natsorted进行排序的签名列表：

- Alexandr Shurigin

---

这不是一个100%正确的解决方案，但我认为这是目前最好的。例如，当您有更多混合格式时，如BA 0001，BA 0010，BA 30，像natsort这样的库可以正确排序。 - Oskar Persson

当然，这是一个非常有趣的问题。我会尝试通过更多的例子和格式来深入检查这个案例。我还有一些想法。 - Alexandr Shurigin

我在考虑是否可以使用一些正则表达式解决方案来拆分不同的部分，并在捕获组上进行排序。但是我不确定在所有不同的数据库后端中该如何实现。例如，使用(.*)\D(?:0*)(?!$)(\d*)$，您可以将前缀和后缀作为单独的组获取：https://regex101.com/r/iasgsz/1 - Oskar Persson

@OskarPersson，请现在检查，我相信它现在可以完美地运行，并且至少可以涵盖99％的情况！由于它不使用任何特殊的数据库函数，因此这个解决方案应该适用于任何后端数据库。 - Alexandr Shurigin

:facepalm: :) 没有注意到那一行，一直专注于不同前缀的解决方案。 - Alexandr Shurigin

Answer 2

如果您不介意针对特定数据库，请使用RawSQL()来注入SQL表达式以解析您的“signature”字段，然后使用结果注释记录集；例如（PostgreSQL）：

queryset = (
    Item.objects.annotate(
        right_part=RawSQL("cast(split_part(signature, ' ', 2) as int)", ())
    ).order_by('right_part')
)

如果你需要支持不同的数据库格式，可以检测活跃的引擎并相应地提供适当的表达式。

RawSQL() 优点在于它明确地说明了何时何地应用特定于数据库的功能。

如@schillingt所指出，Func() 也可以是一种选择。另一方面，我会避免使用extra()，因为它可能很快就被弃用（参见：https://docs.djangoproject.com/en/2.2/ref/models/querysets/#extra）。

证明（针对 PostgreSQL）：

class Item(models.Model):
    signature = models.CharField('Signatur', max_length=50)

    def __str__(self):
        return self.signature

-----------------------------------------------------

import django
from django.db.models.expressions import RawSQL
from pprint import pprint
from backend.models import Item


class ModelsItemCase(django.test.TransactionTestCase):

    def test_item_sorting(self):
        signatures = [
            'BA 1',
            'BA 10',
            'BA 100',
            'BA 2',
            'BA 1002',
            'BA 1000',
            'BA 1001',
        ]
        for signature in signatures:
            Item.objects.create(signature=signature)
        pprint(list(Item.objects.all()))
        print('')

        queryset = (
            Item.objects.annotate(
                right_part=RawSQL("cast(split_part(signature, ' ', 2) as int)", ())
            ).order_by('right_part')
        )

        pprint(list(queryset))

        self.assertEqual(queryset[0].signature, 'BA 1')
        self.assertEqual(queryset[1].signature, 'BA 2')
        self.assertEqual(queryset[2].signature, 'BA 10')
        self.assertEqual(queryset[3].signature, 'BA 100')
        self.assertEqual(queryset[4].signature, 'BA 1000')
        self.assertEqual(queryset[5].signature, 'BA 1001')
        self.assertEqual(queryset[6].signature, 'BA 1002')

结果：

test_item_sorting (backend.tests.test_item.ModelsItemCase) ... [<Item: BA 1>, <Item: BA 10>, <Item: BA 100>, <Item: BA 2>, <Item: BA 1002>, <Item: BA 1000>, <Item: BA 1001>]

[<Item: BA 1>, <Item: BA 2>, <Item: BA 10>, <Item: BA 100>, <Item: BA 1000>, <Item: BA 1001>, <Item: BA 1002>]
ok

----------------------------------------------------------------------
Ran 1 test in 0.177s

- Mario Orlandi

---

这对于特定的示例似乎非常有效，但我认为一个被接受的答案应该针对更通用的解决方案，无论是多个数据库后端还是更多的值模式。 - Oskar Persson

@OskarPersson，一般来说我同意你的观点，大多数情况下，如果不是全部，我更喜欢编写通用的、与数据库无关的代码；这当然是ORM提供的一个巨大优势。然而，这可能会带来一些代价：有时候，你被迫去规范化数据和/或失去一些有效的数据库特定优化。最终，权衡取舍取决于单个应用程序，你可能会遇到这样的情况，在放弃一些普遍性的同时，为你的项目获得实际优势。 - Mario Orlandi

一种妥协的方法可能是向数据库添加一个视图，以更方便地公开底层数据，然后使用Meta.managed=False标记的Django模型来镜像db-view；但这在原始问题分配的用例中可能会过度。 - Mario Orlandi

@OskarPersson：重新考虑后，我确实同意RawSQL在这里不是最好的选择；我从这次演讲中学到了很多……感谢您的评论； - Mario Orlandi

Answer 3

一种简单的方法是添加另一个字段，仅用于排序：

class Item(models.Model):
    signature = models.CharField('Signatur', max_length=50)
    sort_string = models.CharField(max_length=60, blank=True, editable=False)

    class Meta:
        ordering = ['sort_string']

    def save(self, *args, **kwargs):
        parts = self.signature.split()
        parts[2] = "{:06d}".format(int(parts[2]))
        self.sort_string = "".join(parts)
        super().save(*args, **kwargs)

根据数据更新和读取频率的不同，这可能非常高效。每当项目更新时，sort_string会被计算一次，但它随时可用作简单字段。很容易调整sort_string的计算方式以满足您的确切要求。

在开发过程中，将重新保存操作添加到您的管理界面也可能非常有用：

def re_save(modeladmin, request, queryset):
    for item in queryset:
        item.save()
re_save.short_description = "Re-save"

class ItemAdmin(admin.ModelAdmin):
    actions = [re_save, ]
    ....

因此，触发重新计算非常容易。

- Mark Bailey

Answer 4

我假设您的签名字段遵循以下模式：AAA 123，即字母后跟一个空格，然后是数字（整数）。

Item.objects.extra(select={
    's1': 'cast(split_part(signature, \' \', 2) as int)', 
    's2': 'split_part(signature, \' \', 1)'
}).order_by('s2', 's1')

- Airith

Answer 5

如果您想要得到类似于BA 1、BA 1000等的命名方式，最简单的方法是将数据存储为BA 0001、BA 0002等形式，然后使用order by进行排序即可。 否则，您需要使用Python应用一个映射器来转换列表，并使用Python逻辑重新排序。

- gxmad

---

我认为你也应该考虑那些你无法控制数据存储方式的情况。而且在Python中进行排序并不总是可扩展或可行的解决方案。 - Oskar Persson

在这种情况下，您可以通过直接注入SQL代码来编辑Jango获取数据的方式，就像Django文档中所做的那样：https://docs.djangoproject.com/en/2.2/topics/db/sql/#mapping-query-fields-to-model-fields - gxmad

是的，我认为如何使用这些工具来解决这个问题并得出答案，这正是@n2o和我正在寻找的。 - Oskar Persson

Answer 6

我认为这将是一个简单的解决方案，但显然并不是。对于您提出的好问题，我表示赞赏。这是我建议的方法：

• 阅读有关其他人如何在Postgres / DB级别上解决此问题，并确定最适合自己的处理方式。您是否需要自定义类型，可以使用简单的正则表达式等等
• 根据上述内容，在Django迁移中实现该解决方案。您可能需要创建一个类型，这可以通过自定义SQL迁移完成。或者也许您需要在数据库级别创建一个函数。
• 利用新的postgres工件。这部分肯定会很复杂。您可能需要使用{{link3：.extra}}或{{link4：Func}}来访问函数或类型。

这是可能的，但肯定需要进行一些数据库更改和非典型的Django使用。

- schillingt

Answer 7

进一步阐述我之前的建议和@Alexandr Shurigin提出的有趣解决方案，现在我提出另一个选项。

这个新方案将“签名”分成两个字段：

- code：一个可变长度的字母数字字符串 - weight：一个数值，可能带有前导0需要忽略

给定：

    [
        'X 1',
        'XY 1',
        'XYZ 1',
        'BA 1',
        'BA 10',
        'BA 100',
        'BA 2',
        'BA 1002',
        'BA 1000',
        'BA 1001',
        'BA 003',
    ]

预期结果是：

    [
        'BA 1',
        'BA 2',
        'BA 003',
        'BA 10',
        'BA 100',
        'BA 1000',
        'BA 1001',
        'BA 1002',
        'X 1',
        'XY 1',
        'XYZ 1',
    ]

所有计算都以通用的方式委托给数据库，这要归功于django.db.models.functions模块。

    queryset = (
        Item.objects.annotate(
            split_index=StrIndex('signature', Value(' ')),
        ).annotate(
            left=Substr('signature', Value(1), 'split_index', output_field=CharField()),
            right=Substr('signature', F('split_index'), output_field=CharField()),
        ).annotate(
            code=Trim('left'),
            weight=Cast('right', output_field=IntegerField())
        ).order_by('code', 'weight')
    )

更加简洁但可读性较差的解决方案如下：

    queryset = (
        Item.objects.annotate(
            split_index=StrIndex('signature', Value(' ')),
        ).annotate(
            code=Trim(Substr('signature', Value(1), 'split_index', output_field=CharField())),
            weight=Cast(Substr('signature', F('split_index'), output_field=CharField()), output_field=IntegerField())
        ).order_by('code', 'weight')
    )

我真正需要的是一个“IndexOf”函数来计算“split_index”，作为第一个空格或数字的位置，从而实现真正的自然排序行为（例如接受“BA123”和“BA 123”）。

证明：

import django
#from django.db.models.expressions import RawSQL
from pprint import pprint
from backend.models import Item
from django.db.models.functions import Length, StrIndex, Substr, Cast, Trim
from django.db.models import Value, F, CharField, IntegerField


class ModelsItemCase(django.test.TransactionTestCase):

    def test_item_sorting(self):

        signatures = [
            'X 1',
            'XY 1',
            'XYZ 1',
            'BA 1',
            'BA 10',
            'BA 100',
            'BA 2',
            'BA 1002',
            'BA 1000',
            'BA 1001',
            'BA 003',
        ]
        for signature in signatures:
            Item.objects.create(signature=signature)
        print(' ')
        pprint(list(Item.objects.all()))
        print('')

        expected_result = [
            'BA 1',
            'BA 2',
            'BA 003',
            'BA 10',
            'BA 100',
            'BA 1000',
            'BA 1001',
            'BA 1002',
            'X 1',
            'XY 1',
            'XYZ 1',
        ]

        queryset = (
            Item.objects.annotate(
                split_index=StrIndex('signature', Value(' ')),
            ).annotate(
                code=Trim(Substr('signature', Value(1), 'split_index', output_field=CharField())),
                weight=Cast(Substr('signature', F('split_index'), output_field=CharField()), output_field=IntegerField())
            ).order_by('code', 'weight')
        )
        pprint(list(queryset))

        print(' ')
        print(str(queryset.query))
        self.assertSequenceEqual(
            [row.signature for row in queryset],
            expected_result
        )

对于sqlite3，生成的查询语句如下：

SELECT 
    "backend_item"."id", 
    "backend_item"."signature", 
    INSTR("backend_item"."signature",  ) AS "split_index", 
    TRIM(SUBSTR("backend_item"."signature", 1, INSTR("backend_item"."signature",  ))) AS "code", 
    CAST(SUBSTR("backend_item"."signature", INSTR("backend_item"."signature",  )) AS integer) AS "weight" 
FROM "backend_item" 
ORDER BY "code" ASC, "weight" ASC

而对于PostgreSQL：

SELECT 
    "backend_item"."id", 
    "backend_item"."signature", 
    STRPOS("backend_item"."signature",  ) AS "split_index", 
    TRIM(SUBSTRING("backend_item"."signature", 1, STRPOS("backend_item"."signature",  ))) AS "code", 
    (SUBSTRING("backend_item"."signature", STRPOS("backend_item"."signature",  )))::integer AS "weight" 
FROM "backend_item" 
ORDER BY "code" ASC, "weight" ASC

- Mario Orlandi

---

“4” 是从哪里来的？ - Oskar Persson

mmmmhhh 必须是一个拒绝..让我检查和纠正一下 ;) 应该是“字符串结束”。 - Mario Orlandi

好的发现 ;) 现在已经修复。length=None 表示 Substr 的字符串结尾。 - Mario Orlandi

这个问题的限制在于你只能使用空格作为分隔符。"BA 1"、"BA 2"、"BA 10"、"BA1"、"BA2"、"BA10" 无法正确排序。 - Oskar Persson

虽然与@alexandr-shurigin的答案结合起来可能有效。只需在排序字段的末尾添加按原始字符串长度排序，我就可以使上面的示例正确排序。 - Oskar Persson

@OskarPersson：没错！我知道这个限制。不确定在最一般的情况下字符串长度是否真的有用：一个可变长度的“左”字母代码，后跟零个或多个空格，然后是可能以零个或多个'0'开头的数字代码。在这里有用的是检测第一个空格或第一个数字的位置，以相应地拆分原始字符串。我在django.db.models.functions中找不到任何帮助。 - Mario Orlandi

Answer 8

假设签名字段的格式是固定的（带有单个空格，第二部分是数字：[^ ]+ \d+），我们可以将其拆分为两个部分 - base_name（字符串）和sig_value（int）。

此外，您不需要SimpleListFilter（它具有不同的目的 - 创建过滤器！）。 您只需覆盖get_queryset方法即可：

from django.contrib import admin
from django.db.models import F, IntegerField, TextField, Value
from django.db.models.functions import Cast, StrIndex, Substr

from .models import Item

@admin.register(Item)
class ItemAdmin(admin.ModelAdmin):
    def get_queryset(self, request):
        qs = super(ItemAdmin, self).get_queryset(request)
        return qs.annotate(
            # 1-indexed position of space
            space=StrIndex("name", Value(" ")),

            # part of text before the space
            base_name=Substr("name", 1, F("space") - 1, output_field=TextField()),

            # cast part of text after the space as int
            sig_value=Cast(Substr("name", F("space")), IntegerField()),
        ).order_by("base_name", "sig_value")

- jatinderjit