Hi, I am new to Ruby on Rails and - of course - have a problem...
I have cities and I want to define neighborcities. I want to do this
via a second table (cities_cities), where the city_ids are going to be
connected. Now, if city 1 is a neighbor of city 2, then city 2 is a
neighbor of city 1, so to keep it consistent, I want to save it in the
database with the smaller city_id as city_id1, the higher id as
city_id2.
Then my city Model would get the definition
<pre>
has_and_belongs_to_many :neighborcities
:class_name => "City"
</pre>
<pre>
cities
----------------
id
name
cities_cities
-----------------
city_id1
city_id2
</pre>
But then I am stuck, because with this definition
<pre>
has_and_belongs_to_many :neighborcities
:class_name => "City",
:foreign_key => "city_id1",
:association_foreign_key => "city_id2"
</pre>
I only get the cities where city_id1 is defined in the database, not
the other way round.
Then I thougt about putting a finder_sql
<pre>
:finder_sql => "SELECT cities.* FROM cities
LEFT OUTER JOIN cities_cities cc1 ON cities.id = cc1.cities_id2
LEFT OUTER JOIN cities_cities cc2 ON cities.id = cc2.cities_id1
WHERE (cc1.cities_id1 = #{id} OR cc2.cities_id2 = #{id} )"
</pre>
But the #{id} does not return the expected id of the city I am
requesting, but some high number (546214050).
Now: am I going the best way for my project at all? What would be the
best way to connect 2 cities with each other (same problem would be
products has_and_belongs_to_many :similarprodudcts)? There has to be a
simpler solution.
And if I have to use the finder_sql: how would the update_sql look
like?
Any help appreciated,
thanks, greetings from Berlin,
Ina
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
gene.tani-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org
2007-Aug-15 11:04 UTC
Re: has_and_belongs_to_many :neigborcities
On Aug 15, 1:07 am, ina <gatzm...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> Hi, I am new to Ruby on Rails and - of course - have a problem... > > I have cities and I want to define neighborcities. I want to do this > via a second table (cities_cities), where the city_ids are going to be > connected. Now, if city 1 is a neighbor of city 2, then city 2 is a > neighbor of city 1, so to keep it consistent, I want to save it in the > database with the smaller city_id as city_id1, the higher id as > city_id2. > Then my city Model would get the definition > <pre> > has_and_belongs_to_many :neighborcities > :class_name => "City" > </pre> > > <pre> > cities > ---------------- > id > name > > cities_cities > ----------------- > city_id1 > city_id2 > </pre> > > But then I am stuck, because with this definition > <pre> > has_and_belongs_to_many :neighborcities > :class_name => "City", > :foreign_key => "city_id1", > :association_foreign_key => "city_id2" > </pre> > I only get the cities where city_id1 is defined in the database, not > the other way round. > > Then I thougt about putting a finder_sql > <pre> > :finder_sql => "SELECT cities.* FROM cities > LEFT OUTER JOIN cities_cities cc1 ON cities.id = cc1.cities_id2 > LEFT OUTER JOIN cities_cities cc2 ON cities.id = cc2.cities_id1 > WHERE (cc1.cities_id1 = #{id} OR cc2.cities_id2 = #{id} )" > </pre> > > But the #{id} does not return the expected id of the city I am > requesting, but some high number (546214050). > > Now: am I going the best way for my project at all? What would be the > best way to connect 2 cities with each other (same problem would be > products has_and_belongs_to_many :similarprodudcts)? There has to be a > simpler solution. > And if I have to use the finder_sql: how would the update_sql look > like? >This sounds like what people have coded for networks of friends, i.e. self-referential many to many, without the complication of inviting people and having people be pending friends http://railsforum.com/viewtopic.php?pid=8007 http://svn.dnite.org/has_many_friends/README --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---