# Non-standard joins for an associated model

**URL:** <https://www.ruby-forum.com/t/non-standard-joins-for-an-associated-model/137552>\
**Category:** Rails\
**Created:** [May 14, 2008, 6:03am UTC](https://www.ruby-forum.com/t/non-standard-joins-for-an-associated-model/137552 "2008-05-14T06:03:07Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Avishai](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/bbe5ce/32.png) [@Avishai](https://www.ruby-forum.com/u/Avishai)\
**Post date:** [May 14, 2008, 6:03am UTC](https://www.ruby-forum.com/t/non-standard-joins-for-an-associated-model/137552/1 "2008-05-14T06:03:07Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![Avishai](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/bbe5ce/32.png) [@Avishai](https://www.ruby-forum.com/u/Avishai)\
**Post date:** [May 14, 2008, 8:19pm UTC](https://www.ruby-forum.com/t/non-standard-joins-for-an-associated-model/137552/2 "2008-05-14T20:19:47Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Avishai](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/bbe5ce/32.png) [@Avishai](https://www.ruby-forum.com/u/Avishai)\
**Post date:** [May 19, 2008, 3:53pm UTC](https://www.ruby-forum.com/t/non-standard-joins-for-an-associated-model/137552/3 "2008-05-19T15:53:33Z")

</div>

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-

---

<div class="post-metadata">

**Author:** ![Avishai](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/bbe5ce/32.png) [@Avishai](https://www.ruby-forum.com/u/Avishai)\
**Post date:** [May 19, 2008, 4:15pm UTC](https://www.ruby-forum.com/t/non-standard-joins-for-an-associated-model/137552/4 "2008-05-19T16:15:30Z")

</div>

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