How to join model with attribute of other (related) model (plotting data from child)

Question

In an app using the chartkick gem:

I have the following database table relations:

class User < ActiveRecord::Base
  has_many :games
  contains id, email
end

class Game < ActiveRecord::Base
  has_many :levels
  belongs_to :user
  #contains id, user_id, name

  accepts_nested_attributes_for :levels
end

class Level < ActiveRecord::Base
  belongs_to :game
  #contains id, score and game_id
end

How do i plot the score acquired by each player? And then the average score acquired by each player each game?

I want to do something like:

<%= column_chart Level.group(:user.email).sum(:score) %>

I managed to do:

<%= column_chart Level.group(:game_id).sum(:score) %>

The entire code can be found here

score 2 · Accepted Answer · answered Aug 28 '13 at 20:51

"How do i plot the score acquired by each player?"

First add this to User:

has_many :levels, :through => :games

Then get the total score per player with:

 User.includes(:levels).group(:email).sum(:score)

"And then the average score acquired by each player each game?"

User.includes(:levels).group(:email, :game_id).average(:score)

This returns a hash with a tuple (email, game_id) as key and the average as value. Grouping by email is only to be able to get some user identification back in the results but there should be a nicer way to do it somehow.

score 0 · Answer 2 · answered Aug 14 '13 at 13:14

0

You can join the tables using includes,

arel = Level.includes(:user, :game)

Then use appropriate query as you want to,

arel = arel.group('user.email')

answered Aug 14 '13 at 13:14

sumitshining

159
1
9

:user, :game is never declared what are they to be? – David Karlsson Aug 14 '13 at 13:47
You have declared two models, User and Game. :user and :game refer to there table names in the database. To be specific they must be users and games I guess. – sumitshining Aug 14 '13 at 17:44
<%arel = Level.includes(:users, :games)%> <%= column_chart arel.group('user.email').sum(:score) %> The last line throws: Association named 'users' was not found on Level; perhaps you misspelled it? – David Karlsson Aug 14 '13 at 18:22
At first, I would suggest you to do this in your action rather than in the view itself. – sumitshining Aug 19 '13 at 11:40
Secondly, you have to have belongs_to :user in Level model too. – sumitshining Aug 19 '13 at 12:52
None of your suggestions work/make any difference. Neither does your original answer – David Karlsson Aug 22 '13 at 07:49
<%arel = Level.includes(:games)%> <%= column_chart arel.group('game').sum(:score) %> Just gives **Association named 'games' was not found on Level; perhaps you misspelled it?** – David Karlsson Aug 22 '13 at 07:51

How to join model with attribute of other (related) model (plotting data from child)

2 Answers2