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
has_and_belongs_to_many :neighborcities
:class_name => "City"
cities
cities
id
name
cities_cities
city_id1
city_id2
But then I am stuck, because with this definition
has_and_belongs_to_many :neighborcities
:class_name => "City",
:foreign_key => "city_id1",
:association_foreign_key => "city_id2"
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
: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} )"
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