How to sort results based on join table in Rails?

Question

I have Account model,

class Account < ActiveRecord::Base
  has_many :account_games
  def score
    account_games.map(&:score).sum
  end
end

And AccountGame :

class AccountGame < ActiveRecord::Base
  belongs_to :account
end

What is the best way to get accounts with the highest score? as you can see, score is the summation of related account_games score field.

Thanks

jvnill · Accepted Answer · 2013-02-21 08:07:29Z

1

try

Account.joins(:account_games).sum('account_games.score', group: :account_id, order: 'sum_account_games_score')

which will give you a hash where the keys are the account_ids and the values are the total score.

You can pass a limit option to accounts to get the top x accounts. something like

Account.limit(10).joins(:account_games).sum('account_games.score', group: :account_id, order: 'sum_account_games_score DESC')

answered Feb 21, 2013 at 8:07

jvnill

29.6k5 gold badges86 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Eki Eqbal Over a year ago

I thought I was trying to do close thing, but I wasn't even close :) anyways this gave me something like => {125=>#<BigDecimal:7ffdf1a67ba0,'0.0',9(18)>, 115=>#<BigDecimal:7ffdf1a67a88,'0.0',9(18)>, 126=>#<BigDecimal:7ffdf1a67740,'0.0',9(18)>, 116=>#<BigDecimal:7ffdf1a67538,'0.0',9(18)>, 117=>#<BigDecimal:7ffdf1a67290,'0.0',9(18)>}

Eki Eqbal Over a year ago

mm but isn't there any more readable way to do so, like if u want to make more queries based on created_at , weather at account , or account_games ?

Eki Eqbal Over a year ago

and why do i receive BigDecimal instead of values ?

Eki Eqbal Over a year ago

Let me try to be more specific, what if I have email filed in account , and wanna show the result as [email, score] how am i supposed to do so ?

jvnill Over a year ago

change the group: to group: 'accounts.email'. you can still pass conditions to sum or call where before you call sum

|

Collectives™ on Stack Overflow

How to sort results based on join table in Rails?

1 Answer 1

10 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related