From: Harold Hausman <hhausman@...>
Date: 2005-06-07T10:11:00+09:00
Subject: Re: Rails/ActiveRecord Nuby question.

On 6/5/05, Charles Steinman <acharlieblue@gmail.com> wrote:
> 
> Harold Hausman wrote:
> > I have two tables in my DB, one which keeps track of teams and another
> > which keeps track of matches. (two teams can play a match and there is
> > one winner and one loser)  The difficult bit seems to be that the
> > team's id can be stored in either one of two columns in the matches
> > table.
> >
> > I feel like I should be able to setup the Team model to have a
> > collection of the matches which that team has participated in and
> > here's what I've tried so far:
> >
> > class Team < ActiveRecord::Base
> >       has_many :matches, :finder_sql =>
> >               "select * from matches " +
> >               "where matches.one = #{id} " +
> >               "or matches.two = #{id}"
> > end
> >
> > Now this 'works' but it seems that the id that it is inserting into
> > the SQL string is the ruby unique id for the Team object that is
> > created. What I want is the id of the team which is stored in the
> > database. Is this stored in the Team object anywhere? or am I going at
> > this all backwards?
> 
> I haven't used this particular feature of Rails, but I think what you
> want is to use single quotes around the SQL snippets rather than double
> quotes. Double quotes evaluate #{expressions like this} as soon as
> they're created, while ActiveRecord wants to evaluate it later once it
> has a Team object to get the id from.

Perfect! This was exactly the problem, perhaps this should be added to
the activeRecord documentation (or be made more prominent if its
already in there somewhere).

Now another question. ;) Now that each match that the team has
participated in is associated with the team it would be really handy
if the teams involved in each match were associated with that match
(so I can have access to their human readable names and such.) Heres
what I tried:

class Match < ActiveRecord::Base
	has_one :team_one, :class_name => "Team", :conditions => 'id = #{one}'
	has_one :team_two, :class_name => "Team", :conditions => 'id = #{two}'
end

*note: 'one' and 'two' are the names of the columns in the matches
table where I store the team ids that participated in the match.

What I was hoping was that each Match object would then have two new
child objects called team_one and team_two which would be Team
objects. This did not work, near as I can tell it formulated some sql
string which contained some match_id gibberish in the where clause and
there is no match_id column in the teams table. (maybe this is how I'm
supposed to know to use has_and_belongs_to_many)

What I've ended up doing is using has_many, and then using :finder_sql
to completely override the sql statement to select just the team I'm
looking for which works and creates a single element array but results
in some slightly hack-ish useage of operator[] passing 0 to get the
first object (i.e. match.team_one[0]["name"] ) which isint a big deal
at all.

But... It seems to me that it might be valuable to have :finder_sql as
an option for has_one for situations like this where I don't want to
bother with another table just to associate teams with matches and use
has_and_belongs_to_many.

Sorry for the rambling post, and thanks kindly for the quick-like and
useful/accurate response on the single vs. double quote thing. Long
live this community and rails and everything else sweet in this world.
(:

-Harold