Rails 4 scope for SQL statement

Question

I have 3 models, Player, Detail and Hero. A Player has many Details, a Detailbelongs to a Hero. Now I want to retrieve all Heroes a Player has played. I came up with this so far.

Hero.where("id IN (SELECT hero_id FROM details WHERE player_id = 1)").group("id")

How would I write a scope for it so I can also pass the Player to the scope? This is what I got so far, but it only groups the Details. I would also like to count every Heroso at the end I have x times Hero1, x times Hero2 and so on.

scope :heroes, ->(player)  { where('player_id = ?', player.id).group("id") }

This scope is in the Detail model. I don't know if it is the best place, since I want it to return Heroes and not Details.

daiikota · Accepted Answer · 2013-09-13 14:33:31Z

2

Ok finally after quite some time I figured out how to get most played Heroes with a scope.

scope :mostplayed, ->(player) { select('details.*, count(heros.id) AS hero_count').joins(:hero).where('player_id = ?', player.id).group('hero_id').order('hero_count DESC').limit(3) }

Maybe it is not the best solution, but it does exactly what I want it to do.

answered Sep 13, 2013 at 14:33

daiikota

1333 silver badges13 bronze badges

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

Comments

Alter Lagos · Accepted Answer · 2013-09-12 20:17:16Z

1

I think you just need to use has_many :through or has_and_belongs_to_many, so if you use:

player.heroes

That will bring all the heroes related with that player. If you don't need duplicated heroes, you could use

player.heroes.uniq

Now, related about sum every hero, maybe you want to sum how many details has each hero? if that's not what you want, please explain it better.

answered Sep 12, 2013 at 20:17

Alter Lagos

12.6k2 gold badges74 silver badges98 bronze badges

3 Comments

daiikota Over a year ago

No, I don't want a relationship between a Player and a Hero because that would need an additional table. I want to sum up how many times a Playerhas played a Hero. I need a scope that does exactly what the Hero.wheredoes.

Alter Lagos Over a year ago

@daiikota But you already have that aditional table, which is Detail, I cannot see what's the problem with just define the relationship between these two models through details

daiikota Over a year ago

I wish I knew earlier that you can chain has many relationships with has_many through :/

Collectives™ on Stack Overflow

Rails 4 scope for SQL statement

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related