ruby-on-rails - 给定记录和顺序条件，查找之后或之前的记录

Question

例如，您有一个按优先级排序的项目列表。您有 10,000 件商品!如果您向用户显示单个项目，您如何为用户提供按钮以查看上一个项目或下一个项目(这些项目是什么)？

您可以将项目的位置传递到项目页面并在 SQL 查询中使用 OFFSET。这样做的缺点是，除了必须传递一个可能会改变的数字之外，数据库无法跳转到偏移量；它必须读取每条记录，直到到达第 9001 条记录。这很慢。寻找解决方案后，我找不到，所以我写了 order_query .

order_query使用相同的 ORDER BY 查询，但还包括一个 WHERE 子句，该子句排除当前记录之前(对于下一个)或之后(对于上一个)的记录。

下面是标准的示例(使用上面的 gem):

p = Issue.find(31).relative_order_by_query(Issue.visible, 
      [[:priority, %w(high medium low)],
       [:valid_votes_count, :desc, sql: '(votes - suspicious_votes)'],
       [:updated_at, :desc],
       [:id, :desc]])

p.before     #=> ActiveRecord::Relation<...>
p.previous   #=> Issue<...>
p.position   #=> 5
p.next       #=> Issue<...>
p.after      #=> ActiveRecord::Relation<...>

我刚刚在这里重新发明了轮子吗？我对在后端执行此操作的其他方法非常感兴趣。

在内部，此 gem 构建了一个查询，该查询取决于当前记录的订单值，如下所示:

SELECT ... WHERE    
x0 OR
y0 AND (x1 OR
        y1 AND (x2 OR
                y2 AND ...))
ORDER BY ...
LIMIT 1

在哪里x对应>/<条款和y至 =条款(用于解决关系)，每个订单标准。

来自测试套件日志的示例查询:

-- Current record: priority='high' (votes - suspicious_votes)=4 updated_at='2014-03-19 10:23:18.671039' id=9
SELECT  "issues".* FROM "issues"  WHERE 
  ("issues"."priority" IN ('medium','low') OR 
   "issues"."priority" = 'high' AND (
       (votes - suspicious_votes) < 4 OR 
       (votes - suspicious_votes) = 4 AND (
           "issues"."updated_at" < '2014-03-19 10:23:18.671039' OR 
           "issues"."updated_at" = '2014-03-19 10:23:18.671039' AND 
           "issues"."id" < 9)))   
ORDER BY 
  "issues"."priority"='high' DESC, 
  "issues"."priority"='medium' DESC, 
  "issues"."priority"='low' DESC, 
  (votes - suspicious_votes) DESC, 
  "issues"."updated_at" DESC, 
  "issues"."id" DESC
LIMIT 1

Answer 1

我找到了另一种方法，它使用来自 SQL '92 的构造标准(Predicates 209)，行值构造比较谓词:

Let Rx and Ry be the two row value constructors of the comparison predicate and let RXi and RYi be the i-th row value constructor elements of Rx and Ry, respectively. "Rx comp op Ry" is true, false, or unknown as follows:

• "x = Ry" is true if and only if RXi = RYi for all i.
• "x <> Ry" is true if and only if RXi <> RYi for some i.
• "x < Ry" is true if and only if RXi = RYi for all i < n and RXn < RYn for some n.
• "x > Ry" is true if and only if RXi = RYi for all i < n and RXn > RYn for some n.

我找到了一个例子 in this article通过 Markus Winand .行值构造函数比较谓词可以这样使用:

SELECT *
  FROM sales
 WHERE (sale_date, sale_id) < (?, ?)
 ORDER BY sale_date DESC, sale_id DESC

这大致等同于此查询:

SELECT *
  FROM sales
 WHERE sale_date < ? OR (sale_date = ? AND sale_id < ?)
 ORDER BY sale_date DESC, sale_id DESC

首先要注意的是，要直接使用它，所有顺序组件必须在同一方向，否则需要更多操作。另一个是，尽管是标准的，但大多数数据库都不支持行值比较谓词(在 postgres 上有效)。