Has_many, belongs_to with multiple foreign keys

Question

I'm trying to attribute matches to a club through a has_many, belongs_to relationship. However, in matches, I need to set the club either as a home_team or away_team. To solve this I'm using two foreign_keys.

class Club < ActiveRecord::Base
  has_many :matches
end

class Match < ActiveRecord::Base
  belongs_to :home_team, class_name: 'Club', foreign_key: 'home_team_id'
  belongs_to :away_team, class_name: 'Club', foreign_key: 'away_team_id'
end

This sets the clubs nicely on the match using home_team_id and away_team_id.

However, I can't access all of a club's matches through Club.matches.

ERROR:  column matches.club_id does not exist

How can I change my relationship so I can do this?

Answer 1

You could define foreign keys

class Club < ActiveRecord::Base
  has_many :home_matches, class_name: 'Match', foreign_key: 'home_team_id'
  has_many :away_matches, class_name: 'Match', foreign_key: 'away_team_id'
end

But I suspect this will cause more problems as you'd presumably want to get all matches and order by date, which you could do by doing two queries and adding the results and sorting but that is frankly messy.

My initial thought it that you ought to be looking at a has many through relationship it you want to be able to do @club.matches

class Club < ActiveRecord::Base
  has_many :club_matches
  has_many :matches, through: :club_matches
end

class ClubMatch < ActiveRecord::Base
  belongs_to :club
  belongs_to :match
  #will have an attribute on it to determine if home or away team
end

class Match < ActiveRecord::Base
  has_many :club_matches
  has_many :clubs, through: :club_matches
end

Then you'd be able to do @club.matches

Just my initial thought and someone may well come up with a better solution

Presumably though you could just do a query without the association which might be better and less refactoring for you. For example

class WhateverController < ApplicationController

  def matches
    @club = Club.find(params[:club_id)
    @matches = Match.where("home_team_id = :club_id OR away_team_id = :club_id", {club_id: @club.id}).order(:date)
  end