活动记录对关系进行排序，其中包含按辅助属性排序的最后一个空值和非空值

Question

我有一个模型，Prospect.我想按某个值对潜在客户进行排序(例如updated_at ASC(，但是我还想对排序列表进行平分，以便首先显示lead_id IS NOT NULL的所有潜在客户，按updated_at ASC排序，然后最后显示lead_id IS NULL的所有潜在客户按updated_at ASC排序。我不在乎lead_id是什么，只在乎任何lead_id IS NOT NULL被撞到列表开头的记录。我需要能够对数据集进行分页，并且查询需要相对高效(周转时间应远低于 500 毫秒，理想情况下低于 100 毫秒(。

第一种方法

我首先尝试使用以下查询来完成此操作，但是这不起作用(对于我的用例(，因为潜在客户是按lead_id排序的(正如您对此查询所期望的那样(，这是唯一的，因此使辅助排序实际上无用。

Prospect.order("lead_id ASC nulls last, updated_at ASC")

第二种方法

我尝试了Andrey Deineko建议的(略有修改的(方法。这将以正确的顺序返回整个数据集，但无法将两个单独的关系合并为一个可以分页的关系。因此，为了对数据集进行分页，我需要将表中的每一行实例化到内存中。对于几十条记录来说，这是可以接受的，但肯定不是 20k+。

# prospect.rb
scope :with_leads,    -> { where.not(lead_id: nil) }
scope :without_leads, -> { where(lead_id: nil) }
scope :ordered,       -> { order(:total_clicks_count) } 
[Prospect.with_leads.ordered, Prospect.without_leads.ordered].flatten

第三种方法

我意识到我可以获取潜在客户 ID 的排序列表(带和不带lead_id(，并使用它来获取按 id 排序的完整数据集。这完成了我需要的，并且可以正常工作几十或几百条记录，但不适用于 20k+ 记录。

lead_ids = Prospect.where.not(lead_id: nil).pluck(:id)
prospect_ids = Prospect.where(lead_id: nil).pluck(:id)
prospects = Prospect.order_by_ids([lead_ids, prospect_ids].flatten)

这是order_by_ids的来源：

class ApplicationRecord < ActiveRecord::Base
self.abstract_class = true
def self.order_by_ids(ids)
# https://stackoverflow.com/a/26777669/4139179
order_by = ["CASE"]
ids.each_with_index do |id, index|
order_by << "WHEN #{self.name.underscore}s.id='#{id}' THEN #{index}"
end
order_by << "END"
order(order_by.join(" "))
end
end

第二种方法的问题在于，每 1000 条记录最多需要 1 秒来为每个子集(有和没有lead_id的潜在客户(构建列表或有序 ID，然后使用它按顺序获取整个数据集。

有没有更好的方法可以返回按某些属性排序的整个数据集(以可以分页的方式((例如updated_at ASC(有lead_id的潜在客户在列表的顶部，而没有的潜在客户在底部？

Answer 1

在PostgreSQL中使用ORDER BY"column"时，默认情况下NULL值将排在最后。所以

Prospect.order(:lead_id, :updated_at)

应该做这个伎俩。

您的实际需求：

在实践中的效果是，当用户查看其列表时 销售前景，那些已转换为潜在客户的潜在客户将显示 列表中的第一个，即使列表按其他属性排序也是如此。

# prospect.rb
scope :with_leads,    -> { where.not(lead_id: nil) }
scope :without_leads, -> { where(lead_id: nil) }
scope :ordered,       -> { order(:total_clicks_count) } 

然后使用这些范围向用户呈现：

Prospect.with_leads.ordered.each do
#...
end
Prospect.without_leads.ordered.each do
#...
end