Jackson Cunningham Jackson Cunningham - 2 months ago 5
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

try this:

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