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.
--
Posted via http://www.ruby-forum.com/.
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.. -- Posted via http://www.ruby-forum.com/.
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. -- Posted via http://www.ruby-forum.com/.
On Jul 11, 3:16 pm, "Älphä Blüë" <rails-mailing-l...-ARtvInVfO7m5VldFQK4jKA@public.gmane.orgt> wrote:> 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> <% @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. > -- > Posted viahttp://www.ruby-forum.com/.
On Sat, Jul 11, 2009 at 12:30 PM, Frederick Cheung<frederick.cheung-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> 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 ? > > FredIt''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.. -- Posted via http://www.ruby-forum.com/.
On 11 Jul 2009, at 17:55, Rick wrote:> > On Sat, Jul 11, 2009 at 12:30 PM, Frederick > Cheung<frederick.cheung-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> 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"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> > >
Frederick Cheung 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 ) > > FredThe 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.. -- Posted via http://www.ruby-forum.com/.
On Jul 11, 6:34 pm, "Älphä Blüë" <rails-mailing-l...-ARtvInVfO7m5VldFQK4jKA@public.gmane.orgt> 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. >> 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> -- > Posted viahttp://www.ruby-forum.com/.
On Jul 11, 5:55 pm, "Älphä Blüë" <rails-mailing-l...-ARtvInVfO7m5VldFQK4jKA@public.gmane.orgt> 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)> > > 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> > 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.. > -- > Posted viahttp://www.ruby-forum.com/.
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? -- Posted via http://www.ruby-forum.com/.
On Jul 11, 7:33 pm, "Älphä Blüë" <rails-mailing-l...-ARtvInVfO7m5VldFQK4jKA@public.gmane.orgt> wrote:> > class Team > has_many :schedules > has_many :opponents :through => :schedules > end > > class Schedule > belongs_to :team > end > > Is that correct? >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 -- Posted via http://www.ruby-forum.com/.
Ä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" > endAnd 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. -- Posted via http://www.ruby-forum.com/.
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:> > Ä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. > > -- > Posted via http://www.ruby-forum.com/. > > >
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 %>
--
Posted via http://www.ruby-forum.com/.
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.. -- Posted via http://www.ruby-forum.com/.
Ä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-sbuyVjPbboAdnm+yROfE0A@public.gmane.org -- Posted via http://www.ruby-forum.com/.
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-sbuyVjPbboAdnm+yROfE0A@public.gmane.orgAh 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.. -- Posted via http://www.ruby-forum.com/.
Ä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-sbuyVjPbboAdnm+yROfE0A@public.gmane.org -- Posted via http://www.ruby-forum.com/.
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-sbuyVjPbboAdnm+yROfE0A@public.gmane.orgHi 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... -- Posted via http://www.ruby-forum.com/.
Älphä Blüë wrote:> 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.Then Week 3 may be unrealistic. I speak from experience with a sports app on a similar timescale.> 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...Yeah, good luck (seriously, not sarcastically). But I suspect that between learning Rails and building the app, you might have bit off more than you can quickly chew. Best, -- Marnen Laibow-Koser http://www.marnen.org marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org -- Posted via http://www.ruby-forum.com/.