How do you get a list from the has_many complex active record participating in a subset of descending objects

Question

It’s hard for me to get a list of games associated with a hierarchical parental relationship when several foreign keys are implemented in the middle relation.

Given object of the NFC League, find all its game objects [G1,G3,G4]

 # id :integer not null, primary key # name :string class League has_many :teams # has_many :games, :through => :teams (Is there some way to do this?) end # id :integer not null, primary key # team_name :string # league_id :integer class Team belongs_to :league has_many :home_games, :foreign_key => team_a_id, :source => :game has_many :away_games, :foreign_key => team_b_id, :source => :game end # id :integer not null, primary key # game_name :string # team_a_id :integer not null # team_b_id :integer not null class Game belongs_to :home_team, :class_name => Team belongs_to :away_team, :class_name => Team end 

Data examples:

 LEAGUE - TEAM - GAME --------------------------------- AFC - PATRIOTS - Home Away G1(PATRIOTS vs DALLAS) G2(PATRIOTS vs PITTSBURG) PITTSBURG - G2(PATRIOTS vs PITTSBURG) NFC - DALLAS - G1(PATRIOTS vs DALLAS) G3(DALLAS vs GREENBAY) G4(DALLAS vs SEATTLE) GREENBAY G3(DALLAS vs GREENBAY) SEATTLE G4(DALLAS vs SEATTLE) 

The answer will be the Rails 4 answer. Particular attention can be paid to the RAILS 5 answer if the Rails 4 alternative is very inefficient.

 nfc = League.where(name: 'NFC').first # <answer> puts nfc.games ## array containing objects [G1,G2,G3] 

Im task having with is home_team / away_team and combining data from foreign keys.

Answer 1

I am going to give an answer because the first solution from @meagar requires two SQL queries instead of one (also, is it not a SQL syntax error if there are no commands in the league?), And the second solution will contain duplicate game instances if both teams were from the same league.

In general, I try to avoid unions in my reusable areas, because they force the request to specify a certain "form". So I would write something like this:

 class Game # ... scope :for_league, ->(league_id) { where(<<-EOQ, league_id) EXISTS (SELECT 1 FROM teams t WHERE t.id IN (games.team_a_id, games.team_b_id) AND t.league_id = ?) EOQ } # ... end 

This SQL method, by the way, is called a "correlated subquery." I admit that this looks weird the first time you see it, but it's a pretty normal thing. You can see that the subquery "reaches" for the games link. Your database should not have problems with its optimization (taking into account the indexes on your foreign keys), but conceptually speaking, it performs a subquery once per row in the games table.

Answer 2

A possible solution is to define the games method on the League , which will find all the games in which any foreign key points to one of its teams:

 class League has_many :teams def games Game.where('team_a_id in (:ids) or team_b_id in(:ids)', ids: teams.pluck(:id)) end end 

You can accomplish the same thing with join :

 Game.joins('inner join teams on teams.id = games.team_a_id or teams.id = games.team_b_id').where('teams.league_id = ?', id)