1

I have a scores table with both score and time per user. I want to calculate both averages grouped by user. I can successfully calculate one of them but not sure how to do both at once.

@scores = SpellingScore.where(:user_id => users).average(:score, :group => :user)

Will produce sql like the following

SELECT AVG(`spelling_scores`.`score`) AS average_score, user_id AS user_id 
FROM `spelling_scores` 
WHERE (`spelling_scores`.`user_id` IN (78767, 78772, 78775)) GROUP BY user_id

I know how to do it in SQL but can't work out the ActiveRecord way.

This is what I want to do...

SELECT AVG(`spelling_scores`.`score`) AS average_score, AVG(`spelling_scores`.`time`) AS average_time, user_id AS user_id 
FROM `spelling_scores` 
WHERE (`spelling_scores`.`user_id` IN (78767, 78772, 78775)) GROUP BY user_id

Cheers,

Tim

Andrew Marshall
  • 95,083
  • 20
  • 220
  • 214
Tim
  • 2,235
  • 4
  • 23
  • 28

3 Answers3

4

Thanks for your help Macarthy.

I ended up doing it this way

SpellingScore.select("AVG(`spelling_scores`.`score`) AS average_score, 
AVG(`spelling_scores`.`time`) AS average_time, COUNT(*) AS question_count, user_id AS 
user_id").where(:user_id => users).group(:user_id).includes(:user).order('users.last')

At least I've retained some ActiveRecord chaining.

Tim
  • 2,235
  • 4
  • 23
  • 28
0

Wouldn't this work SpellingScore.group(:user).average(:score) Can't test this without a schema though

deiga
  • 1,587
  • 1
  • 13
  • 32
0

Just use SQL. Forget the ActiveRecord way, SQL is better for something like this. If you want to keep your logic in your model just create a new method in your model

macarthy
  • 3,074
  • 2
  • 23
  • 24
  • So does that mean there is no way to do it with ActiveRecord? It seems like such as simple thing. I want to use the new query chaining cause I am going to add more conditions that will come from search fields. – Tim Mar 01 '11 at 01:08