Django QuerySet切片返回意外结果

Question

我正在动态编写Django查询，并收到基于切片参数的意外结果。例如，如果我请求queryset[0:10]和querset[10:20]，我会在query2中收到一些与在query1中找到的项相同的项。

四处搜索，我面临的问题类似于：

简单Djanko查询生成令人困惑的Queryset结果

除了我为查询定义了orderby，这样它看起来就不会完全匹配。

正在查看我的两个查询的querset.query。。。。

queryset[0:10]生成：

SELECT "intercache_localinventorycountsummary"."id",
"intercache_localinventorycountsummary"."part", 
"intercache_localinventorycountsummary"."site", 
"intercache_localinventorycountsummary"."location",
"intercache_localinventorycountsummary"."hadTransactionsDuring"
FROM   "intercache_localinventorycountsummary" 
ORDER BY "intercache_localinventorycountsummary"."hadTransactionsDuring" DESC
LIMIT 10

queryset[10:20]生成：

SELECT "intercache_localinventorycountsummary"."id",
"intercache_localinventorycountsummary"."part", 
"intercache_localinventorycountsummary"."site", 
"intercache_localinventorycountsummary"."location",
"intercache_localinventorycountsummary"."hadTransactionsDuring"
FROM   "intercache_localinventorycountsummary" 
ORDER BY "intercache_localinventorycountsummary"."hadTransactionsDuring" DESC 
LIMIT 10 OFFSET 10

根据请求，我列出了Django生成的字面SQL，并针对DB手动运行它。

查询1的结果：

id  |  part   | site | location | hadTransactionsDuring
------+---------+------+----------+-----------------------
2787 | 2217-1  | 01   | Bluebird | t
2839 | 2215    | 01   | 2600 FG  | t
2558 | R4367   | 01   | 2600 Raw | t
2637 | 4453    | 01   | 2600 FG  | t
2810 | 1000    | 01   | 2600 FG  | t
2531 | 3475    | 01   | 2600 FG  | t
2526 | 4596Z   | 01   | 2550 FG  | t
2590 | 3237-12 | 01   | 2600 Raw | t
3077 | 4841Y   | 01   | 2600 FG  | t
2919 | 3407    | 01   | 2600 FG  | t

查询2的结果：

id  |     part     | site | location | hadTransactionsDuring
------+--------------+------+----------+-----------------------
2598 | 2217-2       | 01   | 2600 Raw | t
2578 | 2216-5       | 01   | 2600 Raw | t
2531 | 3475         | 01   | 2600 FG  | t
3010 | 3919         | 01   | 2600 FG  | t
2558 | R4367        | 01   | 2600 Raw | t
2637 | 4453         | 01   | 2600 FG  | t
2526 | 4596Z        | 01   | 2550 FG  | t
2590 | 3237-12      | 01   | 2600 Raw | t
2570 | R3760-BRN-GS | 01   | 2600 Raw | f
2569 | 4098         | 01   | 2600 FG  | f

(您可以看到两个查询都返回了id 2558、2637、2526、2590)

你猜我做错了什么吗？我似乎从根本上误解了QuerySet切片的工作原理。

更新：

数据库模式如下。。。按非索引字段排序时，结果排序是否不可靠

d intercache_localinventorycountsummary
Table "public.intercache_localinventorycountsummary"
Column         |           Type           |                                     Modifiers
-----------------------+--------------------------+------------------------------------------------------------------------------------
id                    | integer                  | not null default nextval('intercache_localinventorycountsummary_id_seq'::regclass)
_domain_id            | integer                  |
_created              | timestamp with time zone | not null
_synced               | timestamp with time zone |
_active               | boolean                  | not null default true
dirty                 | boolean                  | not null default true
lastRefresh           | timestamp with time zone |
part                  | character varying(18)    | not null
site                  | character varying(8)     | not null
location              | character varying(8)     | not null
quantity              | numeric(16,9)            |
startCount            | timestamp with time zone |
endCount              | timestamp with time zone |
erpCountQOH           | numeric(16,9)            |
hadTransactionsDuring | boolean                  | not null default false
quantityChangeSince   | numeric(16,9)            |
hadManualDating       | boolean                  | not null
variance              | numeric(16,9)            |
unitCost              | numeric(16,9)            |
countCost             | numeric(16,9)            |
varianceCost          | numeric(16,9)            |
Indexes:
"intercache_localinventorycountsummary_pkey" PRIMARY KEY, btree (id)
"intercache_localinventorycount__domain_id_5691b6f8cca017dc_uniq" UNIQUE CONSTRAINT, btree (_domain_id, part, site, location)
"intercache_localinventorycountsummary__active" btree (_active)
"intercache_localinventorycountsummary__domain_id" btree (_domain_id)
"intercache_localinventorycountsummary__synced" btree (_synced)
Foreign-key constraints:
"_domain_id_refs_id_163d40e6b21ac0f9" FOREIGN KEY (_domain_id) REFERENCES intercache_domain(id) DEFERRABLE INITIALLY DEFERRED

Answer 1

问题在于：

ORDER BY "intercache_localinventorycountsummary"."hadTransactionsDuring" DESC

显然，您已经在查询或模型的元选项中显式重写了排序(请参阅模型元选项：排序)。

如果您想按hadTransactionsDuring进行订购，但有可预测的订购，您应该添加第二个订购，以解决第一个订购具有相同价值的情况。例如：

queryset.order_by("-hadTransactionsDuring", "id")

请记住RDBMS，无论是PostgreSQL还是MySQL，都不会保证任何顺序，除非用ORDER BY明确指定。大多数查询通常按主键的顺序返回，但这更像是一个巧合，这取决于表存储的内部实现，而不是你可以依赖的东西。换句话说，你不能假设Django queryset是在order_by中指定的字段之外的任何字段上排序的。