Reputation: 496
I am trying to get records of each category limited to 5, but I ended up returning only 5 records.
How can I translate this sql statement in Rails ActiveRecord?
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())
I was just able to do the following, which returned only 5 records as mentioned above:
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")
Upvotes: 1
Views: 1218
Reputation: 20232
Can you use 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()))
Or are you looking to totally replace it with AR Calls?
-- pulling up code from comment----
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
Upvotes: 1
Reputation: 125574
select j.*
from
jobs j
inner join (
select
row_number() over (partition by category_id order by id) rn,
id
from jobs
where
jobkey_confirmation IS NOT NULL
AND EXTRACT(MONTH from created_at) = EXTRACT(MONTH from now())
) i on i.id = j.id
where rn <= 5
Upvotes: 1