基于多个子模型条件/状态的排序

Question

我有一个房间模型，它有_many interval。interval有一个start_date和end_date属性。我想根据以下条件预订房间

1-没有间隔的房间2-带有start_date> Date.today的任意间隔的房间3-房间没有任何间隔start_date> Date.tody

我想用postgresql查询而不是模型上的方法来排序满足1的房间，然后满足2的房间，然后是3的房间。

当我使用下面的查询/作用域找出哪些房间没有间隔时没有问题

joins("LEFT OUTER JOIN intervals ON rooms.id = intervals.room_id").where("intervals.id is NULL")

我在单个查询中按3个条件排序有问题，我尝试了以下

Room.joins("LEFT OUTER JOIN intervals ON rooms.id = intervals.room_id").order("CASE WHEN intervals.id IS NULL THEN 1 END ASC")

这个查询的问题是，如果你在它上调用distinct，你会得到错误PG::InvalidColumnReference: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list，另一个问题是，即使你在这里调用distinct，它也能正常工作，你只会按一个条件排序，这是第一个1。

我的想法是生成一个有case条件的函数，根据3个条件给出生成列的值，并给出第一个值1，第二个值2，第三个值3，并按此计算值排序，每当我尝试它时，虽然我受到上面提到的错误的打击。如有任何帮助，不胜感激。

这是我结合所有3个条件的试验:

Room.joins("LEFT OUTER JOIN intervals ON rooms.id = intervals.room_id").group("room.id").order("CASE WHEN room.id IS NULL THEN 1 WHEN COUNT(CASE WHEN intervals.start_date > '#{Date.today.to_s(:db)}' THEN 1 END) > 0 THEN 2 WHEN COUNT(CASE WHEN intervals.start_date > '#{Date.today.to_s(:db)}' THEN 1 END) = 0 THEN 3 END")

然而，这给出了错误:PG::GroupingError: error: column "intervals。id"必须出现在GROUP BY子句中，或者在聚合函数

中使用

Answer 1

在这种"特定"的情况下，我将针对您的特定业务规则对模式进行反规范化。请记住，您可能在某些时候需要分页，并且需要在数据库中进行排序。

为了达到这个效果，我将在模型中添加一列来指示排序优先级:

add_column :rooms, :sort_priority, :integer, null: false, default: 0

然后在我的interval模型中，我将包括一个before_save过滤器，它将设置优先级

before_save -> {
  if (start_date < blah blah blah)
    self.priority = 1
  elsif (blah blah)
    etc...
  end
}

那么我将在我的参考

中反映这一点

class Room
  has_many :intervals, -> {order :priority, :start_date}
end