CODEWITHSUNDEEP
sqlruby-on-railsruby-on-rails-4arel
Josh Smith
Josh Smith

Reputation: 15028

Rails: group by the count of a column

I'm not sure how to best express this question.

Let's say I have a UserSkill, which belongs_to :user and belongs_to :skill. I have a collection of Skills, and from those I have an array of skill_ids, say with .map(&:id).

I can easily use this array to do an IN type query like UserSkill.where(skill_id: skill_ids).

But I want to find the users that have the most skills from my input.

I tried writing this naively as UserSkill.where(skill_id: skill_ids).group("user_skills.user_id").order("count(user_skills.user_id) desc"), but that has a syntax error.

To further clarify, let's say we have User id: 1 and User id: 2. Our result from UserSkill.where(skill_id: skill_ids) is the following:

The result I'd be looking for would be:

What's the right query for this? And how should I be phrasing this question to begin with?

Upvotes: 1

Views: 225

Answers (2)

rossta
rossta

Reputation: 11494

Assuming a has_many association from User to UserSkill, you could try

User.joins(:user_skills).
  group("users.id").
  order("COUNT(users.id) DESC").
  merge(UserSkill.where(skill_id: skill_ids))

Upvotes: 3

John Naegle
John Naegle

Reputation: 8257

In SQL I might write this:

select users.*
from users
  join user_skills on users.id = user_skills.user_id
where 
  user_skills.skill id in (1,2,3)
group by users.id
order by count(*) desc, users.id asc
limit 5

Which might look like this:

User.joins("user_skills on users.id = user_skills.user_id").
     where("user_skills.skill_id" => skill_ids).
     group("users.id").
     order("count(*) desc").
     limit(5)

Upvotes: 1

Related Questions