Non-standard joins for an associated model

Hi,

I have two models, Place and Region. This seems like a good place to have a composite primary key, but seems that Rails doesnt support it.

class Place belongs_to :region   ... end

class Region   has_many :places   ... end

That would be fine, except the data I'm working with and will be importing has the following columns, with the levels representing a level of region (such as state/province, county, district, etc.) Region: country_id, level1, level2, name Place: id, name, ... level1, level2 ..

This raw SQL works for displaying the place along with the name of it's region. How do I achieve the same result through the ActiveRecord Place.find(:all, :include => :region)?

SELECT places.id, places.name, places.country_id, countries.name, regions.name FROM places INNER JOIN regions ON places.country_id = regions.country_id   AND places.admin1 = regions.level1   AND places.admin2 = regions.level2 LEFT JOIN countries ON places.country_id = countries.id

Thanks!

Avishai wrote:

class Place belongs_to :region   ... end

class Region   has_many :places   ... end

How do I achieve the same result through the ActiveRecord Place.find(:all, :include => :region)?

SELECT places.id, places.name, places.country_id, countries.name, regions.name FROM places INNER JOIN regions ON places.country_id = regions.country_id   AND places.admin1 = regions.level1   AND places.admin2 = regions.level2 LEFT JOIN countries ON places.country_id = countries.id

Thanks!

It feels to me like your data model is wrong.

Might work better if you had

Region:   id,   name,   parent_id,   country_id,   type (County, Town, ...)

Country:   id,   name.

Place:   id,   name   region_id

(Country could just be a special case of Region, but maybe you want it seperate somehow)

Country: United Kingdom > >- Region: England > > >----- Region: Greater London > > >-----------Region: Shepherds Bush > > >----------------Region: West 12 Shopping Centre                   >                   Place: "Vue Cinema - Shepherds Bush"

Although that wouldn't be cheap to grab all the parents.

Dunno, depends why you need this relationship.

Thanks. In the end I decided to settle for something in between, since I don't really need a very deep parent/child structure.

I wrote a rake task that goes and fetches the region id (autonumbered) and inserts it into the region_id column, so that it's easier for rails to understand. Since the underlying data won't change much, this seemed to be a decent solution that's easily adaptable to rails.

-Avishai

Avishai wrote:

Thanks. In the end I decided to settle for something in between, since I don't really need a very deep parent/child structure.

-Avishai

On May 14, 2:19�pm, Matthew Rudy Jacobs <rails-mailing-l...@andreas-

Excellent Avishai. Good luck with the project.

I fear sometimes my data models are too crazy to work with.

MatthewRudy