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

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

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 R. 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

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs