How do you get a list from complex active record has_many involved from a subset of downstream objects

Question

I'm having a hard time getting a list of the games involved from a hierarchical parent relationship when multiple foreign keys are implemented on a relationship in the middle.

Given League Object NFC, find all of 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 contain a Rails 4 compliant answer. Special consideration may be awarded to a 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]

The challenge Im having with is the home_team / away_team and combining data from the foreign keys.

Answer 1

I'm going to give an answer, because the first solution by @meagar requires two SQL queries instead of one (also, isn't that a SQL syntax error if a league has no teams?), and the second solution will contain duplicate Game instances if both teams were from the same league.

In general I try to avoid joins in my reusable scopes, since they force the query into a certain "shape". 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 technique is called a "correlated sub-query" by the way. I admit it looks strange the first time you see it, but it is a pretty normal thing to do. You can see that the subquery "reaches out" to reference games. Your database should have no problem optimizing it (given indexes on your foreign keys of course), but conceptually speaking it runs the subquery once per row in the games table.

Answer 2

A possible solution is to define a games method on League that finds all of the games where either 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 a 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)