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