Jackson Cunningham Jackson Cunningham - 1 year ago 38
Ruby Question

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

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 Source

try this:

.select('keywords.name, avg(user_keywords.relevance_score) as score')
.order('score DESC')