django-具有字符和整数的queryset order_by字段 [英] django - queryset order_by field that has characters and integers

问题描述

我有一个模型，该模型具有一个字段（称为 this_field ），该字段存储为 string 。 this_field 中的值的格式为 Char ### -如，它们的值如下： A4 或 A34 或 A55 （请注意，他们将始终拥有相同的第一个字符）。

I have a model that has a field (lets call it this_field) which is stored as a string. The values in this_field are in the form Char### - as in, they have values such as: A4 or A34 or A55 (note that they will always have the same first character).

是否可以选择所有记录并通过此字段对其进行排序？目前，当我执行 .order_by（'this_field'）时，我得到的命令如下：

Is there a way to select all the records and order them by this field? Currently, when I do an .order_by('this_field') I get the order like this:

A1
A13
A2
A25
等...

A1 A13 A2 A25 etc...

我想要的是：

A1
A2
A13
A25
等...

A1 A2 A13 A25 etc...

实现此查询集的任何最佳方法或解决方案是否正确排序？

Any best approach methods or solutions to achieve this query set ordered properly?

推荐答案

查询集排序由数据库后端处理，而不是由数据库后端处理。 Django的。这限制了更改订购方式的选项。您可以加载所有数据并使用Python对其进行排序，也可以在查询中添加其他选项，以通过定义函数使数据库使用某种自定义排序。

Queryset ordering is handled by the database backend, not by Django. This limits the options for changing the way that ordering is done. You can either load all of the data and sort it with Python, or add additional options to your query to have the database use some kind of custom sorting by defining functions.

使用查询集额外（ ）函数将允许您通过执行自定义SQL进行排序，但是以降低可移植性为代价。

Use the queryset extra() function will allow you to do what you want by executing custom SQL for sorting, but at the expense of reduced portability.

在您的示例中，它将将输入字段分为两组数据（初始字符和剩余的整数值）可能就足够了。然后，您可以对两个列都进行排序。下面是一个示例（未经测试）：

In your example, it would probably suffice to split the input field into two sets of data, the initial character, and the remaining integer value. You could then apply a sort to both columns. Here's an example (untested):

qs = MyModel.objects.all()

# Add in a couple of extra SELECT columns, pulling apart this_field into
# this_field_a (the character portion) and this_field_b (the integer portion).

qs = qs.extra(select={
              'this_field_a': "SUBSTR(this_field, 1)",
              'this_field_b': "CAST(substr(this_field, 2) AS UNSIGNED)"})

额外调用将两个新字段添加到 SELECT 调用中。第一个子句提取字段的第一个字符，第二个子句将字段的其余部分转换为整数。

The extra call adds two new fields into the SELECT call. The first clause pulls out the first character of the field, the second clause converts the remainder of the field to an integer.

现在应该可以 order_by 在这些字段上。通过在 order_by 中指定两个字段，顺序首先适用于字符字段，然后适用于整数字段。

It should now be possible to order_by on these fields. By specifying two fields to order_by, ordering applies to the character field first, then to the integer field.

例如

qs = qs.order_by('this_field_a', 'this_field_b')

此示例在MySql和SQLite上均适用。还应该可以创建一个仅用于排序的额外字段，从而允许您在 order_by（）调用中仅指定一个字段。

This example should work on both MySql and SQLite. It should also be possible to create a single extra field which is used only for sorting, allowing you to specify just a single field in the order_by() call.

这篇关于django-具有字符和整数的queryset order_by字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！