Loose associations in ActiveRecord

Question

I'm new to Ruby on Rails and I'm trying to build a relationship between the classes Club, Sponsor and Match.

The relationhip has to be like:

• One Club has zero to many Sponsors
• One Sponsor has zero to many Matches
• One Match has zero to many Sponsors

My models look like this

class Match < ApplicationRecord
  belongs_to :team
  has_many :matchplayers
  has_many :players, through: :matchplayers
  has_many :sponsors
end

class Club < ApplicationRecord
    has_many :teams
    has_many :sponsors
    accepts_nested_attributes_for :teams, :reject_if => :all_blank, :allow_destroy => true
end

class Sponsor < ApplicationRecord
    belongs_to :club
end

and my migrations file for the Sponsor model looks like this:

class CreateSponsors < ActiveRecord::Migration[5.1]
  def change
    create_table :sponsors do |t|
      t.text :name
      t.text :url
      t.text :imgUrl
      t.references :club, foreign_key: true

      t.timestamps
    end

    add_reference :matches, :sponsor, index: true
    add_foreign_key :matches, :sponsor
  end
end

I have no problems retrieving sponsors for each club instance but I'm having trouble retrieving the sponsors associated with each match.

In my matches_controller.rb I have this

def show 
    @match = Match.find(params[:id])
    render :json => @match.to_json(:include => [:players, :sponsors])
end

But when I try to run it the script fails. with the error message "no such column: sponsors.match_id" as the script tries to run the following SQL statement

SELECT "sponsors".* FROM "sponsors" WHERE "sponsors"."match_id" = ?

What I'd really like it to do would be to run the following statement

SELECT "sponsors".* 
FROM   "sponsors" 
       LEFT JOIN "matches" 
               ON "matches"."sponsor_id" = "sponsors"."id" 
WHERE  "matches"."id" = ? 

And placing the resulting array into the output JSON's "sponsors" attribute.

I have been looking into the different Active Record association types and I feel like the type of association I need for this task is looser than the ones described in the documentation.

Answer 1

You need many-to-many relationship. In rails where are 2 ways to do this. You can read about this here. In general you will need to add has_and_belongs_to_many to Sponsor and to Match. And create 'join-model' which will contain match_id + sponsor_id. In this way ActiveRecord will be able to create suitable SQL query due to 'join-table'.