如何在 rails 中查询查询的结果(使用 rails 和 postgres 查询"DISTINCT ON"的结果)

Question

简体版:我想查询另一个查询的结果，以便选择一个更有限的结果集。但是，添加where子句会重写第一个查询，而不是对结果进行处理，因此我无法得到所需的答案。

细节:我有两种型号，check和ticks。

第一个查询使用DISTINCT ON并收集所有的"检查"和所有相关的标记，但只返回最近的标记。我把它作为模型中的作用域。

在我的控制器中

  def checklist
  #Filter the results by scope or return all checks with latest tick
  case params[:filter]
    when "duebylastresult"
      @checks = Check.mostrecenttickonly.duebylastresult
    when "duebydate"
      @checks = Check.mostrecenttickonly.duebydate
    else
      @checks = Check.mostrecenttickonly
    end
  end

在模型中，第一个作用域(工作):

scope :mostrecenttickonly, -> {
includes(:ticks)
.order("checks.id, ticks.created_at DESC")
.select("DISTINCT ON (checks.id) *").references(:ticks)
}

生成以下SQL:

  Parameters: {"filter"=>""}
  SQL (1.0ms)  SELECT DISTINCT ON (checks.id) *, 
"checks"."id" AS t0_r0, 
"checks"."area" AS t0_r1, "checks"."frequency" AS t0_r2, 
"checks"."showinadvance" AS t0_r3, "checks"."category" AS t0_r4, 
"checks"."title" AS t0_r5, "checks"."description" AS t0_r6, 
"checks"."created_at" AS t0_r7, "checks"."updated_at" AS t0_r8, 
"ticks"."id" AS t1_r0, "ticks"."result" AS t1_r1, 
"ticks"."comments" AS t1_r2, "ticks"."created_at" AS t1_r3, 
"ticks"."updated_at" AS t1_r4, "ticks"."check_id" AS t1_r5 
FROM "checks" LEFT OUTER JOIN "ticks" 
ON "ticks"."check_id" = "checks"."id"  
ORDER BY checks.id, ticks.created_at DESC

得到这个结果后，我想只显示值等于或大于3的刻度，所以作用域:

   scope :duebylastresult, -> { where("ticks.result >= 3") }

生成SQL

  Parameters: {"filter"=>"duebylastresult"}
  SQL (1.0ms)  SELECT DISTINCT ON (checks.id) *, 
"checks"."id" AS t0_r0, 
"checks"."area" AS t0_r1, "checks"."frequency" AS t0_r2,
"checks"."showinadvance" AS t0_r3, "checks"."category" AS t0_r4, 
"checks"."title" AS t0_r5, "checks"."description" AS t0_r6, 
"checks"."created_at" AS t0_r7, "checks"."updated_at" AS t0_r8, 
"ticks"."id" AS t1_r0, "ticks"."result" AS t1_r1, 
"ticks"."comments" AS t1_r2, "ticks"."created_at" AS t1_r3, 
"ticks"."updated_at" AS t1_r4, "ticks"."check_id" AS t1_r5 
FROM "checks" LEFT OUTER JOIN "ticks" 
ON "ticks"."check_id" = "checks"."id" 
WHERE (ticks.result >= 3)  
ORDER BY checks.id, ticks.created_at DESC

据我所知，WHERE语句在DISTINCT ON子句之前起作用，所以我现在有'结果>= 3的最新滴答'，而我正在寻找'最新滴答'，只有结果>= 3'。

希望这是有意义的&提前感谢!

编辑-我得到的和我需要的示例:

The Data:
Table Checks:
ID: 98 Title: Eire
ID: 99 Title: Land
Table Ticks:
ID: 1 CheckID: 98 Result:1 Date: Jan12
ID: 2 CheckID: 98 Result:5 Date: Feb12
ID: 3 CheckID: 98 Result:1 Date: Mar12
ID: 4 CheckID: 99 Result:4 Date: Apr12
First query returns the most recent result, like;
Check.ID: 98  Tick.ID: 3  Tick.Result: 1 Tick.Date: Mar12
Check.ID: 99  Tick.ID: 4  Tick.Result: 4 Tick.Date: Apr12
Second query currently returns the most recent result where the result is =>3, like;
Check.ID: 98  Tick.ID: 2  Tick.Result: 5 Tick.Date: Feb12
Check.ID: 99  Tick.ID: 4  Tick.Result: 5 Tick.Date: Apr12
When I really want:
Check.ID: 99  Tick.ID: 4  Tick.Result: 5 Tick.Date: Apr12
(ID 98 doesn't show as the last Tick.Result is 1).

Answer 1

你可以试试下面的命令，看看它是否引导你进入了正确的方向:

    scope :just_a_test, -> {
    includes(:ticks)
    .order("checks.id")
    .where("ticks.created_at = (SELECT MAX(ticks.created_at) FROM ticks WHERE ticks.check_id = checks.id)")
    .where("ticks.result >= 3")
    .group("checks.id")
    }

Answer 2

我不确定我真的理解:mostrecenttickonly范围的意义，因为你只是加载检查。

话虽这么说，如果你只想得到那些最近的刻度结果大于3的检查，我认为最好的方法是使用窗口函数:

check.rb

...
  scope :duebylastresult, -> {
    find_by_sql(
      'SELECT *
       FROM (SELECT checks.*,
                    ticks.id AS tick_ids,
                    ticks.date AS tick_date,
                    ticks.result AS tick_result,
                    dense_rank() OVER (
                      PARTITION BY checks.id
                      ORDER BY ticks.date DESC
                    ) AS tick_rank
             FROM checks
             LEFT OUTER JOIN ticks ON checks.id = ticks.check_id) AS ranked_ticks
       WHERE tick_rank = 1 AND tick_result >= 3;'
    )
  }
...

基本上，我们只是将检查和标记表中的所有内容连接起来，然后添加另一个称为tick_rank的属性，该属性根据其date与具有相同checks.id值的其他行对结果集中的每一行进行排名。

SQL的工作方式是在计算SELECT字段之前计算谓词(WHERE子句中的条件)，这意味着我们不能在此语句中只写tick_rank = 1。

所以我们必须去包装结果的额外步骤(我们别名为ranked_ticks)，然后只是选择一切，并将我们想要的谓词应用到这个外部选择语句。tick_rank必须是1，这意味着它是最近的tick，结果必须是>= 3。

---

edit:我正在使用我链接的那篇文章作为复习，因为我经常忘记SQL语法，但在看了它之后，我认为这将是更高效的(基本上只是等待加入checks直到分区完成后，这样我相信它会做更少的完整扫描):

  scope :duebylastresult, -> {
    find_by_sql(
      'SELECT *
       FROM checks
       LEFT OUTER JOIN 
            (SELECT id AS tick_id,
                    check_id AS check_id,
                    date AS tick_date, 
                    result AS tick_result,
                    dense_rank() OVER (
                      PARTITION BY ticks.check_id
                      ORDER BY ticks.date DESC
                    ) AS tick_rank
             FROM ticks) AS ranked_ticks ON checks.id = ranked_ticks.check_id
       WHERE tick_rank = 1 AND tick_result >= 3;'
    )
  }