SQL:使用 OR 链接的排序范围(Ruby on Rails)

Question

我在 Professor 上有 3 个范围，涉及加入 Professor::AvailabilityPeriod，该模型表示教授有空在一周的给定时间段上课。这是通过将每个周期的开始和结束时间的表示形式存储为从一周开始算起的秒数来实现的，我将其称为 starts_at_sfsow 和 ends_at_sfsow。

Professor::AvailabilityPeriod 有 2 个范围，overlapping(start_time, end_time) 和 contained_within(start_time, end_time)。

我在 Professor 上有 3 个瞄准镜:

class Professor < ActiveRecord::Base
  scope :available_at, -> (opts) do
    start_time = opts.fetch(:start_time)
    end_time = opts.fetch(:end_time)

    includes(:availability_periods).
    joins(:availability_periods).merge(
      self::AvailabilityPeriod.overlaping(
        start_time: start_time,
        end_time: end_time,
      )
    )
  end

  scope :available_around, ->(opts) do
    start_time = opts.fetch(:start_time)
    end_time = opts.fetch(:end_time)

    available_at(
      start_time: start_time - 1.hour,
      end_time: start_time
    ).or available_at(
      start_time: end_time,
      end_time: end_time + 1.hour
    )
  end

  scope :partially_available_at, ->(opts) do
    start_time = opts.fetch(:start_time)
    end_time = opts.fetch(:end_time)

    includes(:availability_periods).
    joins(:availability_periods).merge(
      self::AvailabilityPeriod.contained_within(
        start_time: start_time,
        end_time: end_time,
      )
    )
  end
end

然后，我有一个第 4 个作用域，它对所有其他作用域求和，这就是我将用来在 Controller 中搜索教授的作用域(工作正常):

  scope :available_at_or_around_or_partially, -> (opts) do
    distinct.
      available_at(opts).
      or(available_around(opts)).
      or(partially_available_at(opts))
  end

问题:我需要 available_at 返回的记录首先出现，然后是 available_around，最后是 partially_available_at , 按照这个顺序，我还需要保持它是纯 SQL，这样我可以稍后应用分页；绝对没有内存排序。

我一直在考虑使用子查询，这样我就可以对每个范围进行某种优先级分配，例如:

 distinct.
 where(id: available_at(opts).select("id, 1 AS priority")).   
 or(
   where(id: available_around(opts).select("id, 2 AS priority"))
 ). 
 or(
   where id: partially_available_at(opts).select("id, 3 AS priority")
 )
 order("priority, id")

...但这是不可能的，因为 postgres 不允许我为每个子查询选择超过 1 个字段，这不是事件有效语法。

顺便说一句:如果您对 或 方法的使用感到困惑，那是 where-or gem 附带的，Rails 5 功能的反向移植。

SQL:

Professor.available_at_or_around_or_partially start_time: Time.now, end_time: Time.now.+(2.hours)

SELECT     "professors".*
INNER JOIN "professor_availability_periods"
ON         "professor_availability_periods"."professor_id" = "professors"."id"
WHERE
(
  (
    /* available_at */
    ("professor_availability_periods"."starts_at_sfsow" <= 446340) /* start_time as seconds from start of week */
    AND ("professor_availability_periods"."ends_at_sfsow" >= 453540) /* end time as seconds from start of week*/

    /* available_around */
    OR (
      ("professor_availability_periods"."starts_at_sfsow" <= 442740)
      AND ("professor_availability_periods"."ends_at_sfsow" >= 446340)

      OR

      ("professor_availability_periods"."starts_at_sfsow" <= 453540)
      AND ("professor_availability_periods"."ends_at_sfsow" >= 457140)
    )
  )

  /* partially_available_at */
  OR ("professor_availability_periods"."starts_at_sfsow" >= 446340)
  AND ("professor_availability_periods"."ends_at_sfsow" <= 453540)
)

Answer 1

一直在寻求一些同事的帮助，似乎我一直需要的是UNION ALL .这允许我设置 <number> AS priority对于每个子查询，然后是 ORDER BY priority .这正是我需要的!

SQL:

SELECT DISTINCT "professors".* FROM (
  (SELECT "professors".* FROM
    (
      (SELECT *, 1 AS priority FROM "professors"
        INNER JOIN "professor_availability_periods"
        ON "professor_availability_periods"."professor_id" = "professors"."id"
        WHERE
          ("professor_availability_periods"."starts_at_sfsow" <= 455580)
          AND ("professor_availability_periods"."ends_at_sfsow" >= 462780)
      ) UNION ALL (
        SELECT *, 2 AS priority FROM "professors"
        INNER JOIN "professor_availability_periods"
        ON "professor_availability_periods"."professor_id" = "professors"."id"
        WHERE (
          ("professor_availability_periods"."starts_at_sfsow" <= 451980)
          AND ("professor_availability_periods"."ends_at_sfsow" >= 455580)
          OR
          ("professor_availability_periods"."starts_at_sfsow" <= 462780)
          AND ("professor_availability_periods"."ends_at_sfsow" >= 466380)
        )
      )
    ) professors
  ) UNION ALL (
    SELECT *, 3 AS priority FROM "professors"
    INNER JOIN "professor_availability_periods"
    ON "professor_availability_periods"."professor_id" = "professors"."id"
    WHERE
      ("professor_availability_periods"."starts_at_sfsow" >= 455580)
      AND ("professor_availability_periods"."ends_at_sfsow" <= 462780)
  )
) professors
ORDER BY priority

函数嵌套看起来有点别扭，因为它是由active_record_union生成的，这又让我通过 ActiveRecord 执行此查询:

scope :search_by_availability, -> (opts) do
  available_at(opts).select("*", "1 AS priority").
  union_all(available_around(opts).select("*", "2 AS priority")).
  union_all(partially_available_at(opts).select("*", "3 AS priority")).
  order("priority").distinct(:id)
end