Need some help with a model query

Rails
1

My setup:

class Team
has_many :schedules
end

class Schedule
belongs_to :team
end

Teams Controller:

@team = Team.find(params[:id])
@team_id = params[:id]
@schedule = Schedule.list(@team_id)

Schedule Model:

def self.list(teamid)
named_scope :team_schedule, lambda { { :conditions => [‘team_id = ?’,
teamid] } }
team_schedule :joins => :team, :order => :date_scheduled
end

With my list method everything works great and as I want it too. My
data returns:

@team.name returns the team name
@schedule.opponent returns the team’s opponent
@schedule.location returns the team’s location (home/away/neutral)
@schedule.date_played returns the team’s scheduled date

Great so far…

Now I want to retrieve the team_id for the (opponent) inside the
schedules table. Each team plays approx. 12 opponents. So, I would
like to use an each statement to retrieve that bit of data at the same
time I’m iterating through my table view…

<% @schedule.each do |schedule| %>
<%=h schedule.opponent %>
<%=h schedule.opponent.team_id %>
<% end %>

… I’m uncertain how to retrieve the team_id for the opponent listed in
the schedules table. team_id is the foreign key for the teams table.

Any help on how to write out this query would be appreciated.

2

Adding some shorter useful information to this question:

Schedules table contains the following:

team_id
name
date_scheduled
opponent
location

Teams table contains the following:

id
name

If I make a call to the schedule model I want to retrieve:

(OK) team.name
(OK) schedule.date_scheduled
(OK) schedule.opponent
(OK) schedule.location

(NOTOK) schedule.opponent.team_id

If I run a basic query I’m really only retrieving the following:

1 team_id belonging to team.name
12 date_scheduled returns
12 opponent returns
12 location returns

I need to find the team_id for all 12 opponents that were pulled from
the basic query. This is what I need to figure out how to do…

3

Diagram:

=============================================
| team_id | name | opponent |

| 10 | Florida | Charleston Southern |

| 10 | Florida | Troy |

| 10 | Florida | Tennessee |

| 10 | Florida | Kentucky |

| 10 | Florida | LSU |

| 10 | Florida | Arkansas |

| 10 | Florida | Mississippi St. |

| 10 | Florida | Georgia |

| 10 | Florida | Vanderbilt |

| 10 | Florida | South Carolina |

| 10 | Florida | Florida Int’l |

| 10 | Florida | Florida St. |

=============================================

As you can hopefully see by this diagram, there are 12 rows being
returned that contain these values.

In my teams table the team_ids for each of the opponents are:

===================================
| id | name |

| – | Charleston Southern |

| 12 | Troy |

| 44 | Tennessee |

| 53 | Kentucky |

| 62 | LSU |

| 11 | Arkansas |

| 19 | Mississippi St. |

| 41 | Georgia |

| 59 | Vanderbilt |

| 86 | South Carolina |

| 97 | Florida Int’l |

| 40 | Florida St. |

===================================

Notice that the first team doesn’t exist in my table at all. So, that
opponent won’t be found (they are in division 2). The rest of the teams
are in the team’s table and all have foreign keys assigned for team_id.
However, I can’t figure out how to reference another team within the
same query…

I hope the diagrams help a little.

4

On Jul 11, 3:16 pm, “Älphä Blüë” [email protected]
wrote:

Teams Controller:
team_schedule :joins => :team, :order => :date_scheduled
end

Creating a named scope on the fly like that is really rather odd (and
completely unnecessary). i’m also not sure why you don’t just do
@schedule = team.schedules.find :all, :order => ‘…’
If you want you can add a named scope on schedules with the order you
want and do

@schedule = team.schedules.with_my_ordering

Now I want to retrieve the team_id for the (opponent) inside the
schedules table. Each team plays approx. 12 opponents. So, I would
like to use an each statement to retrieve that bit of data at the same
time I’m iterating through my table view…

Why not put the team_id for the opponent in the schedules table ?

Fred

5

Creating a named scope on the fly like that is really rather odd (and
completely unnecessary). i’m also not sure why you don’t just do
@schedule = team.schedules.find :all, :order => ‘…’
If you want you can add a named scope on schedules with the order you
want and do

It was just some thrown together code - not staying like that
permanently. I create named scopes mostly for pagination or for
reusability in multiple methods.

Why not put the team_id for the opponent in the schedules table ?

I can’t parse the schedules from ncaa.org so I’m having to enter every
team’s schedule by hand into the table initially.

120 teams x 12 rows = 1,440 team_ids…

I would have to look up each team id manually in order to verify and
place them in there. Whereas when I manually enter the data in right
now, I simply am entering schedules via printouts. I would hate to have
to verify 1,440 IDs but if that’s the best way to do this then I will.

The name of the column would have to be different though. I can’t have
two team_id columns in my schedules table.

I was really hoping there was a way to query the teams table and find
the team_id for the name == opponent from the schedules table…

6

On 11 Jul 2009, at 17:55, Rick wrote:

Team team;
Team opponent;

would you just set up another belongs_to like:

belongs_to :opponent, :class_name => “Team”, :foreign_key =>
“opponent_id”

That’s what I would do (except that the :foreign_key is redundant
here: rails will default to #{association_name}_id for a belongs_to )

Fred

7

On Sat, Jul 11, 2009 at 12:30 PM, Frederick
Cheung[email protected] wrote:

Now I want to retrieve the team_id for the (opponent) inside the
schedules table. Each team plays approx. 12 opponents. So, I would
like to use an each statement to retrieve that bit of data at the same
time I’m iterating through my table view…

Why not put the team_id for the opponent in the schedules table ?

Fred

It’s possibly I’m hijacking this thread a bit because I’m still a
Rails noob, but couldn’t opponent by of type Team in another
belongs_to relationship on schedule? Or is that a bad idea? If so, can
someone tell me how you’d model that? From an OO perspective you’d
have

Team team;
Team opponent;

would you just set up another belongs_to like:

belongs_to :opponent, :class_name => “Team”, :foreign_key =>
“opponent_id”

8

On Jul 11, 6:34 pm, “Älphä Blüë” [email protected]
wrote:

Another query should be made against each opponent returned within the
first query finding their subsequent ID in the teams table.

How would I implement such a query? Define each returned opponent as a
param? Pass the param to another method within teams?

This is the issue I would like to see answered. I really should not
have to create another id column in my schedules for the opponents.
Why? It’s redundant data.

an team_id of 10.

I just don’t see why something like this is so difficult to achieve? I
can create the entire query in mysql but I shouldn’t have to create a
complete mysql query in rails for something like this. I’ve defined the
relationships between these tables so they are associated. That means I
should be able to pull any data from either table at anytime…

Well having the names of teams in the schedules table is the redundant
data in my opinion. Associations work through primary keys and so on,
associating via things like names just aren’t that common (because
names are often non unique, subject to change etc…). Having defined
an association via the primary key doesn’t magically define
associations through other attributes.

You could easily call Team.find_by_name to find the team with
appropriate name or add a method to schedule that returned that but I
fail to see why you wouldn’t just store the opponent_id on the
schedules table and be done with it. In recent versions of rails
has_many/belongs_to have a :primary key association for creating
associations that target an attribute other than the primary key, but
again the name isn’t a great candidate for this (more useful when you
have some business provided unique identifier like an ISBN)

Fred

9

Frederick C. wrote:

On 11 Jul 2009, at 17:55, Rick wrote:

Team team;
Team opponent;

would you just set up another belongs_to like:

belongs_to :opponent, :class_name => “Team”, :foreign_key =>
“opponent_id”

That’s what I would do (except that the :foreign_key is redundant
here: rails will default to #{association_name}_id for a belongs_to )

Fred

The opponents are the same teams listed in the teams table. They just
have not been identified yet.

What I see from a logical point of view - using motion…

A team id is found (florida)
A query is sent to schedules for (florida)
A dataset is retrieved for (florida)
Within that dataset are opponents for (florida)

Another query should be made against each opponent returned within the
first query finding their subsequent ID in the teams table.

How would I implement such a query? Define each returned opponent as a
param? Pass the param to another method within teams?

This is the issue I would like to see answered. I really should not
have to create another id column in my schedules for the opponents.
Why? It’s redundant data.

Opponents are also Teams that have opponents in the schedules table…

There should be a way to cross check their name and find their id in
either the schedules table or the teams table. If you see my diagram
above it shows that Florida for instance has 12 opponents but you also
see that the team_id shows Florida as 10.

If I did a search for Florida St. they would have Florida as their week
12 opponent in the schedules table. So, this means that there is a team
name of Florida (somewhere in the schedules table) that already houses
an team_id of 10.

I just don’t see why something like this is so difficult to achieve? I
can create the entire query in mysql but I shouldn’t have to create a
complete mysql query in rails for something like this. I’ve defined the
relationships between these tables so they are associated. That means I
should be able to pull any data from either table at anytime…

10

On Jul 11, 5:55 pm, “Älphä Blüë” [email protected]
wrote:

Creating a named scope on the fly like that is really rather odd (and
completely unnecessary). i’m also not sure why you don’t just do
@schedule = team.schedules.find :all, :order => ‘…’
If you want you can add a named scope on schedules with the order you
want and do

It was just some thrown together code - not staying like that
permanently. I create named scopes mostly for pagination or for
reusability in multiple methods.

Nothing wrong with named scopes, but typically you wouldn’t create one
inside a method like that (just as you wouldn’t create an association
in there either)

now, I simply am entering schedules via printouts. I would hate to have
to verify 1,440 IDs but if that’s the best way to do this then I will.

You could use one of the autocomplete plugins (or even something more
manual) that (at creation time) would look up the team id for you,
based on the name you entered.

The name of the column would have to be different though. I can’t have
two team_id columns in my schedules table.

Of course not. but you could easily have a team_id column and an
opponent_id column
Fred

11

Okay I think I’m going to do some cleanup of these tables…

I’m going to remove name from the schedules table since it already
exists in Teams. I need to create an opponent_id as well in the
schedules table.

Now I just need to make sure I supply the correct associations. Would
it be?..

class Team
has_many :schedules
has_many :opponents :through => :schedules
end

class Schedule
belongs_to :team
end

Is that correct?

12

On Jul 11, 7:33 pm, “Älphä Blüë” [email protected]
wrote:

Is that correct?

you’ll also want an opponent association on Schedule (as rick
suggested - don’t forget the class_name option)

Fred

13

Älphä Blüë wrote:

So…

class Team
has_many :schedules
has_many :opponents :through => :schedules
end

class Schedule
belongs_to :team
belongs_to :opponent, :class_name => “Team”
end

And using this,

how do I reference opponents in my RESTful methods?

For instance, doing this, when I refresh my index page for schedules it
shows both the opponents and opponent_id fields empty in my table.
However, I’ve populated a few of them already in my database.

def Index
@schedules = Schedule.all
end

That’s what I have currently and it doesn’t find opponent or
opponent_id, even though they are listed in the schedules table.

14

This might be of use to you:
http://blog.hasmanythrough.com/2007/10/30/self-referential-has-many-through
because you are doing a self-referential join through the schedules
table.

Worry out the routing after your models are behaving correctly (maybe
a passing test or two?).

Hope this helps

On Jul 11, 2009, at 11:59 AM, Älphä Blüë wrote:

15

So…

class Team
has_many :schedules
has_many :opponents :through => :schedules
end

class Schedule
belongs_to :team
belongs_to :opponent, :class_name => “Team”
end

16

So thanks guys - here is the real fix:

class Team < ActiveRecord::Base
has_many :schedules
has_many :opponents, :through => :schedules
end

class Schedule < ActiveRecord::Base
belongs_to :team
belongs_to :opponent, :class_name => “Team”
end

And in my schedules controller I have:

def index
@schedules = Schedule.all :include => [:team, :opponent]

respond_to do |format|
format.html # index.html.erb
format.xml  { render :xml => @schedules }

end
end

And in my views I can call it by:

<% @schedule.each do |schedule| %>
schedule.team.name
schedule.opponent_id
schedule.opponent.name
<% end %>

17

I’m very happy with the replies on this topic thread and also with the
MIRC for rubyonrails…

I managed to get my tables normalized again, removing the name and the
opponent columns. I also got my clubhouse starter pages up which houses
all of the teams, including links within scheduling to go to another
clubhouse team page.

It looks pretty solid right now. One more step accomplished. I’ve been
putting 8 to 9 hours a day for 6 weeks now into my site trying to get it
ready for football season. When I finally do release it, I’ll showcase
it and you guys can let me know what you think.

It’s my first rails project and it encompasses pretty much everything:

Rails, Javascript, Flash/Flash Executables and I even built many of the
initial views using Dreamweaver CS4 and Photoshop CS4. Hopefully it
will be solid when it’s released…

Thanks again for this bit - it just opened my eyes to some new model
association areas…

18

Älphä Blüë wrote:
[…]

I can’t parse the schedules from ncaa.org so I’m having to enter every
team’s schedule by hand into the table initially.
[…]

Why on earth can’t you parse the schedules? It should not be necessary
to enter data like this manually.

Best,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]

19

Marnen Laibow-Koser wrote:

Älphä Blüë wrote:
[…]

I can’t parse the schedules from ncaa.org so I’m having to enter every
team’s schedule by hand into the table initially.
[…]

Why on earth can’t you parse the schedules? It should not be necessary
to enter data like this manually.

Best,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]

Ah there’s a few reasons Marnen.

It’s not that I can’t - it’s that I won’t.

First, there’s naming conventions to consider for each team name. Each
website that houses schedules name their teams differently:

ESPN names USC → USC
ncaa.org names USC → Southern California

Some sites are more up to date on schedules and some sites are not. In
fact, I haven’t found a single site yet that houses all of the correct
schedule data for each team in division 1, including times (up-to-date)
and we’re 2 months away from the start of the season.

Secondly, my site is very dependent on the team schedules for
statistical calculation. And, I mean extremely dependent. If anything
is wrong with the schedule data it will great affect my statistical
data, especially where factoring strength of schedule and using STD for
offsetting those margins.

I have built many parsers. I have one parser that parses 37 different
statistical categories from ncaa.org and takes approximately 45 seconds
to complete from start to finish. It’s not that I can’t write parsers…

The site I’m creating will house head-to-head virtual matchup scenarios
using a system I created called TSRS (True Statistical Rating of
Strength) which uses advanced mathematics to calculate variance among
all 120 Division I teams in every statistical category.

The old site is still up if you want to see:

http://ncaastatpages.com

… that one is driven in PHP…

The new site will eventually house a subscription service for virtual
matchups so I need everything to be accurate. Therefore, most of the
first run dependent data I’m doing by hand and performing multiple
checks for consistency and then running tests on that consistency. I
need everything to be perfect for the test start of this season…

20

Älphä Blüë wrote:
[…]

First, there’s naming conventions to consider for each team name. Each
website that houses schedules name their teams differently:

ESPN names USC → USC
ncaa.org names USC → Southern California
[…]

Isn’t there an XML feed or something that you could subscribe to where
this is already normalized? I know such things exist for pro football
(I think the provider I worked with was called SportsXML or something
like that).

Best,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]