Non-standard joins for an associated model

Rails
1

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!

2

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.

3

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-

4

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