Django QuerySet 切片返回意外结果

Question

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

四处搜索，我遇到的问题类似于:

Simple Djanqo Query generating confusing Queryset results

除非我为我的查询定义一个 order_by，所以它看起来不是完全匹配。

正在查看我的两个查询的 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，并针对数据库手动运行它。

查询 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 切片的工作原理。

更新:

DB 架构如下......当按非索引字段排序时，结果排序是否不可靠？

\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

显然，您已经在查询或模型的元选项中显式覆盖了排序(参见 Model Meta options: ordering)。

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

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

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