通过交替值订购查询集 [英] Order queryset by alternating value

问题描述

我有以下模型：

class Entry(models.Model):
    name = models.Charfield(max_length=255)
    client = models.Charfield(max_length=255)

client 是客户机的名称，其值可能类似于 facebook ， google ，依此类推。

client is the name of the client as could have values like facebook, google, and so on.

是否可以对查询集进行排序，以便结果交替显示 client ？
我期望的是这样的：

Is it possible to order the queryset so that the result is alternating the values of client? What I expect is something like this:

Entry.objects.order_by('alternate client') --> 

| client   | name   |
| google   | robert |
| facebook | linda  |
| google   | kate   | 
| facebook | jack   |
| google   | nina   |
| facebook | pierre |    

如果有帮助，我正在使用django2.x和postgres。

I am using django2.x and postgres if that helps.

编辑：

一些其他信息/要求。

• 我有大约10到20个不同的客户


• 条目也有一个创建的 DateField。如果可能的话，还应按日期排序


• 我要使用分页进行输入，因此解决方案应使用Django的ORM

• I have about 10 to 20 differtent clients
• Entry does also have a created DateField. If possible result should also be ordered by date
• I want to use pagination for Entry so solution should be using django's ORM

推荐答案

由于您使用Postgres，因此可以使用其窗口函数，该函数跨一组与当前行相关的表行执行计算。另一个很好的信息取决于您使用支持窗口功能的Django2.x（ Django文档），它允许在 Querysets 中添加 OVER 子句。

Since you use Postgres you can use its Window Functions which perform a calculation across a set of table rows that are somehow related to the current row. Another good information relies in the fact that you use Django2.x which supports Window Functions(Django docs) which allows adding an OVER clause to Querysets.

您的用例可以通过单个ORM查询来解决，例如：

Your use-case can be resolved with Single ORM query like:

from django.db.models.expressions import Window
from django.db.models.functions import RowNumber
from django.db.models import F

results = Entry.objects.annotate(row_number=Window(
    expression=RowNumber(),
    partition_by=[F('client')],
    order_by=F('created').desc())
).order_by('row_number', 'client')

for result in results:
    print('Id: {} - client: {} - row_number {}'.format(result.id, result.client, result.row_number))

输出：

Id: 12 - client: facebook - row_number 1
Id: 13 - client: google - row_number 1
Id: 11 - client: facebook - row_number 2
Id: 8 - client: google - row_number 2
Id: 10 - client: facebook - row_number 3
Id: 5 - client: google - row_number 3
Id: 9 - client: facebook - row_number 4
Id: 3 - client: google - row_number 4
Id: 7 - client: facebook - row_number 5
Id: 2 - client: google - row_number 5
Id: 6 - client: facebook - row_number 6
Id: 1 - client: google - row_number 6
Id: 4 - client: facebook - row_number 7

原始SQL看起来像这样：

The raw SQL looks like:

SELECT 
"orm_entry"."id",
"orm_entry"."name",
"orm_entry"."client",
"orm_entry"."created",
ROW_NUMBER() OVER (PARTITION BY "orm_entry"."client" ORDER BY "orm_entry"."created" DESC) AS "row_number" 
FROM "orm_entry" 
ORDER BY "row_number" ASC, "orm_entry"."client" ASC

窗口函数被声明为一个聚合函数，后跟一个OVER子句，该子句精确指示了如何对行进行分组。应用了窗口函数的行组称为分区。

Window functions are declared just as an aggregate function followed by an OVER clause, which indicates exactly how rows are being grouped. The group of rows onto which the window function is applied is called "partition".

您会注意到，我们按'client'对行进行了分组>字段，您可以得出结论，在我们的示例中，我们将有两个分区。第一个分区将包含所有 facebook 条目，第二个分区将包含所有 google 条目。在基本形式上，分区与普通的聚合函数组没有什么不同：只是一组按某些条件视为相等的行，该函数将应用于所有这些行以返回单个结果。

You can notice that we grouped the rows by 'client' field and you can conclude that in our example we will have the two partitions. First partition will contain all the 'facebook' entries and second partition will contain all the 'google' entries. In its basic form, a partition is no different than a normal aggregate function group: simply a set of rows considered "equal" by some criteria, and the function will be applied over all these rows to return a single result.

在您的示例中，我们可以使用 row_number 窗口函数，简单地返回分区中当前行的索引（从1开始）。这帮助我在 order_by中建立了交替输出（行编号，客户端）。

In your example we can use the row_number window function which simply returns the index of the current row within its partition starting from 1. That helped me to establish the alternating output in order_by('row_number', 'client').

其他信息：

Additional information:

如果您要获得这样的订单：

If you want to achieve an order like this:

'facebook'，' facebook'，'google'，'google'，'facebook'，'facebook'，'google'，'google'

或

'facebook'，'facebook'，'facebook'，'google'，'google'，'google'，'facebook'，'facebook '，'facebook'

您将需要对上一个查询进行一次与数学相关的小修改，例如：

You will need to do one small math related modification of the previous query like:

GROUP_SIZE = 2
results = Entry.objects.annotate(row_number=Window(
    expression=RowNumber(),
    partition_by=[F('client')],
    order_by=F('created').desc())
).annotate(row_number=(F('row_number') - 1)/GROUP_SIZE + 1).order_by('row_number', 'client')

for result in results:
    print('Id: {} - client: {} - row_number {}'.format(result.id, result.client, result.row_number))

输出：

Id: 12 - client: facebook - row_number 1
Id: 11 - client: facebook - row_number 1
Id: 8 - client: google - row_number 1
Id: 13 - client: google - row_number 1
Id: 10 - client: facebook - row_number 2
Id: 9 - client: facebook - row_number 2
Id: 3 - client: google - row_number 2
Id: 5 - client: google - row_number 2
Id: 7 - client: facebook - row_number 3
Id: 6 - client: facebook - row_number 3
Id: 1 - client: google - row_number 3
Id: 2 - client: google - row_number 3
Id: 4 - client: facebook - row_number 4

您会注意到 GROUP_SIZE 常数定义每个交替组中的项目数。

You can notice that GROUP_SIZE constant defines how many items will be in the each alternating group.

PS

谢谢您提出这个问题，因为它帮助我更好地理解了窗口函数。

Thank you for asking this question because it helped me to better understand the Window Functions.

快乐的编码:)

这篇关于通过交替值订购查询集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！