ruby-on-rails - 每组 Rails LIMIT

Question

我试图将每个类别的记录限制为 5，但我最终只返回了 5 条记录。

如何在 Rails ActiveRecord 中翻译这条 sql 语句？

SELECT *
FROM "jobs" a
WHERE (
  SELECT COUNT(*)
  FROM jobs
  WHERE ID = a.ID
) <= 5
AND jobkey_confirmation IS NOT NULL
AND EXTRACT(MONTH from created_at) = EXTRACT(MONTH from now())

我只能执行以下操作，如上所述只返回 5 条记录:

scope :confirmed_this_month, where("jobkey_confirmation IS NOT NULL AND EXTRACT(MONTH from created_at) = EXTRACT(MONTH from now())").group("category").limit(5).order("created_at DESC")

Answer 1

可以使用 find_by_sql 吗？

Job.find_by_sql('SELECT * FROM "jobs" a WHERE (SELECT COUNT(*) FROM jobs WHERE ID = a.ID ) <= 5 AND jobkey_confirmation IS NOT NULL AND EXTRACT(MONTH from created_at) = EXTRACT(MONTH from now()))

或者您希望用 AR Calls 完全取代它？

-- 从评论中提取代码----

def self.limited_grouped_jobs 
    find_by_sql("SELECT * FROM jobs a WHERE (SELECT COUNT(*) FROM jobs WHERE ID = a.ID ) <= 5 AND jobkey_confirmation IS NOT NULL AND EXTRACT(MONTH from created_at) = EXTRACT(MONTH from now())")
end