Need some help with a model query

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.

Diagram:

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

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

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...

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"

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..

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"

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

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..

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

> 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)

> 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.

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

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

Fred

So..

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

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

Ä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.

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

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 %>

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..

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
marnen@marnen.org

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..

Marnen Laibow-Koser wrote:

Ä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
marnen@marnen.org

Hi Marnen,

Yeah, eventually I can pull in data using an XML feed (as you
suggested). That will be the best method in the future. I'm kind of
just babying my app with the beginning load data so that I can hand
check things personally. Scheduling seems easy but it's actually quite
difficult.

You have divisions, team names, formal team names, dates, times,
locations, week start containers, and the list goes on and on.

Once I have my site up and running and things are going well, I'll then
be able to work on automating tasks like schedules.

I just wanted you to realize that I do agree with you. I'm definitely
for modular sites with a lot of automation to shorten work loads. I
just have limited time in order to get my site up and running before the
end of week 3 (which is when my subscription service will begin).

I'll look into the company name you provided. I'm sure there's a very
simple way of pulling in schedules that I can modify to work with my
system. However, right now, a lot of my existing app is
self-realization and still in discovery mode. I'm hopeful about getting
it up and running within the next two months but I may have to wait
until next season before I can deliver on my subscription service for
head-2-head...