SQL Query Ruby On Rails

Question

I have two models team and fixture. The fixture model has two columns home_team_id and away_team which contain the foreign key of team's primary key.

I am trying to do a select query where I can get the team name using the home_team_id and away_team_id in the fixture table.

class Fixture < ActiveRecord::Base
    attr_accessible :away_score, :away_team_id, :home_score, :home_team_id, :result, :week
    belongs_to :team, :class_name => Team
end

class Team < ActiveRecord::Base
    attr_accessible :form, :name
    has_many :fixtures, :class_name => Fixture, :foreign_key => :home_team_id
    has_many :fixtures, :class_name => Fixture, :foreign_key => :away_team_id
end

Do I need to do the SQL query in the Fixtures controller, and how can I then show it in my fixture view?

This is what I tried for the query but had no luck. In my fixtures show I had:

<p>
  <b>Home team:</b>
  <%= Team.find_by_sql("SELECT name FROM teams WHERE team.id = fixtures_home_team_id")     %>
</p>

You have 2 has_many :fixtures relations in your Team class, that may be a problem... — Baldrick
– Baldrick, Commented Jan 1, 2013 at 18:36
Your query says FROM teams but your object is named team. In any case, did you get some error message? "Had no luck" doesn't tell us much... — BellevueBob
– BellevueBob, Commented Jan 1, 2013 at 18:39

PinnyM · Accepted Answer · 2013-01-01 18:39:17Z

7

Rewrite your models like this:

class Fixture < ActiveRecord::Base
  attr_accessible :away_score, :away_team_id, :home_score, :home_team_id, :result, :week
  belongs_to :home_team, :class_name => 'Team'
  belongs_to :away_team, :class_name => 'Team'
end

class Team < ActiveRecord::Base
  attr_accessible :form, :name
  has_many :home_fixtures, :class_name => 'Fixture', :foreign_key => :home_team_id
  has_many :away_fixtures, :class_name => 'Fixture', :foreign_key => :away_team_id
end

Now you can do this in your controller:

@fixture = Fixture.find(params[:id])

And this in your view:

<p>Away Team: <%= @fixture.away_team.name %></p>
<p>Home Team: <%= @fixture.home_team.name %></p>

answered Jan 1, 2013 at 18:39

PinnyM

35.6k4 gold badges77 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user1014888 Over a year ago

Thanks great answer, worked perfect, have been over a week trying to figure it out with different sql queries I was writing my own, but your answer made it alot more simple

user1014888 Over a year ago

Do you know any good tutorials or links to any good explanations of using foreign keys within rails? Its just I havn't seen it used in examples I followed before and I presumed Rails just knew it was a foreign key by the fact it was _team_id at the end.

PinnyM Over a year ago

You almost had it correct, except you didn't specify a unique name for each association. The association names are the only part that must be specified, everything else depends on the convention used. I would recommend starting with this guide, and you can follow that up with this railscast for a better explanation.

Collectives™ on Stack Overflow

SQL Query Ruby On Rails

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related