Rails & Active Record: Get the average value of a column after grouping

Question

I have a table user_keywords which :belongs_to :keywords. user_keywords has a column keyword_id, user_id, and relevance_score (float).

keyword table has a column 'name'.

1. I want to group all the user_keywords by their keyword_id.
2. I want to take the average of each of those groups' relevance_score
3. I want to sort groups by the highest relevance score
4. I want to return the name of the keyword from the groups, sorted by highest relevance score.

What is the most efficient way to query this?

Answer 1

try this:

Keyword.joins(:user_keywords)
.select('keywords.name, avg(user_keywords.relevance_score) as score')
.group('keywords.name')
.order('score DESC')
.map(&:name)