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?

I have this problem too with `Users` and `Messages.from_id` and `Messages.to_id`. I'm trying `has_many :messages, ->(u) {where "from_id = ? or to_id = ?", u.id, u.id}, foreign_key: nil` without success. — Chloe, Jul 07 '17 at 20:16

score 5 · Answer 1 · edited May 23 '17 at 12:24

5

My answer to Associations and (multiple) foreign keys in rails (3.2) : how to describe them in the model, and write up migrations is just for you!

As for your code,here are my modifications

class Club < ActiveRecord::Base
  has_many :matches, ->(club) { unscope(where: :club_id).where("home_team_id = ? OR away_team_id = ?", club.id, club.id) }, class_name: 'Match'
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

So any questions?

edited May 23 '17 at 12:24

Community

1
1

answered Nov 04 '16 at 17:18

sunsoft

476
6
10

What does `unscope` do? The first `class_name` doesn't seem necessary. – Chloe Jul 07 '17 at 20:22
2

Wow this worked `has_many :messages, ->(u) {unscope(where: :user_id).where "from_id = ? or to_id = ?", u.id, u.id}` yields `u.messages Message Load (15.6ms) SELECT "messages".* FROM "messages" WHERE (from_id = 1 or to_id = 1)` – Chloe Jul 07 '17 at 20:31

score 3 · Accepted Answer · answered May 07 '14 at 10:01

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

I think you're on the right track with the query. After considering that solution, the association seems unnecessary! Thanks! — Will Viles, May 07 '14 at 10:08
No worries, sometimes you can't see the wood for the trees. Got halfway through writing about has many through then thought you could just do it in a query ignoring the association. — j-dexx, May 07 '14 at 10:10

Has_many, belongs_to with multiple foreign keys

2 Answers2