CODEWITHSUNDEEP
mysqlruby-on-railsrubyactiverecord
Bustikiller
Bustikiller

Reputation: 2498

Include all ids in ActiveRecord query

I am developing a gaming platform and I have the following (simplified) models: 

class Game < ActiveRecord:Base
  has_many :game_players
  has_many :players, through: :game_players
end

class Player < ActiveRecord:Base
  has_many :game_players
  has_many :games, through: :game_players
end

class GamePlayer < ActiveRecord:Base
  belongs_to :game
  belongs_to :player
end

I need to perform an ActiveRecord query that looks for all games played by a certain group of users. For example, given the data:

+---------+-----------+
| game_id | player_id |
+---------+-----------+
|      10 |        39 |
|      10 |        41 |
|      10 |        42 |
|      12 |        41 |
|      13 |        39 |
|      13 |        41 |
+---------+-----------+

I need to find a way to determine which games are played by the players with ids 39 and 41 which, in this case, would be the games with ids 10 and 13. The query I have found up to now is:

Game.joins(:players).where(players: {id: [39, 41]}).uniq

However, this query is returning the games played by any of these players, instead of the games played by both of them.

Upvotes: 2

Views: 132

Answers (2)

Michael Gaskill
Michael Gaskill

Reputation: 8042

This functions more like a SQL INTERSECT, and should give you the results that you need in this case:

Game.joins(:players).where(players: {id: [39,41]}).group('"games"."id"').having('COUNT("games"."id") > 1')

Really, the magic happens by selecting the games where either player is playing, and then grouping by game.id to reduce the results to those having more than one game.id in the result group. It produces the following results from the Rails console:

 => #<ActiveRecord::Relation [#<Game id: 10, created_at: "2016-05-07 01:17:25", updated_at: "2016-05-07 01:17:25">, #<Game id: 13, created_at: "2016-05-07 01:17:25", updated_at: "2016-05-07 01:17:25">]>

Note that only games 10 and 13 (based on the sample data) are returned by this solution. Manual verification shows that only games 10 and 13 had both players 39 and 41 playing.

Upvotes: 1

dp7
dp7

Reputation: 6749

You can give it a try if you can execute two query and intersect the results:

Game.joins(:players).where(players: {id: 39}) & Game.joins(:players).where(players: {id: 41})

Upvotes: 1

Related Questions