Need some help with a model query

rubyonrails-talk
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

Diagram:

3

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

4

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"

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

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

7

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

8

> 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

9

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

Fred

10

So..

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

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

11

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

12

This might be of use to you: has_many :through - Self-referential has_many :through associations   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

13

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

14

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

15

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

16

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