CODEWITHSUNDEEP
ruby-on-railspostgresqlactiverecordruby-on-rails-3.2
janejanejane
janejanejane

Reputation: 496

Rails LIMIT per GROUP

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

Answers (2)

Doon
Doon

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

Clodoaldo Neto
Clodoaldo Neto

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

Related Questions