按分组日期范围进行MySQL排序

Question

我有一个具有以下四个作用域的模型：

scope :active, -> { where("start_at <= '#{Time.now.utc}' AND end_at > '#{Time.now.utc}'") }
scope :ended, -> { where("end_at < '#{Time.now.utc}'") }
scope :coming_soon, -> { where("start_at > '#{Time.now.utc}' AND start_at < '#{Time.now.utc+6.hours}'") }

我希望能够做一些类似的事情(伪代码，只是试着给出一个想法)：

Model.all.order(active, coming_soon, ended)

结果将是该表中的所有行，首先按满足active约束的行排序，然后是满足coming_soon约束的行，最后是满足ended约束的所有行。

Answer 1

我添加了以下作用域，它似乎解决了我的问题：

scope :time_sorted, -> { 
    select("events.*, CASE 
      WHEN start_at <= '#{Time.now.utc}' AND end_at > '#{Time.now.utc}' THEN 1
      WHEN end_at < '#{Time.now.utc}' THEN 2
      WHEN start_at > '#{Time.now.utc}' AND start_at < '#{Time.now.utc+6.hours}' THEN 3 ELSE 4
      END AS formula")
    .order("formula ASC")
}