# Active record relationship problems

**URL:** <https://www.ruby-forum.com/t/active-record-relationship-problems/191377>\
**Category:** Rails\
**Created:** [July 11, 2010, 8:38am UTC](https://www.ruby-forum.com/t/active-record-relationship-problems/191377 "2010-07-11T08:38:57Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Norm\_S](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/9f8e36/32.png) [@Norm\_S](https://www.ruby-forum.com/u/Norm_S)\
**Post date:** [July 11, 2010, 8:38am UTC](https://www.ruby-forum.com/t/active-record-relationship-problems/191377/1 "2010-07-11T08:38:57Z")

</div>

Using RoR 2.2.2 with MySql Server version: 5.0.75-0ubuntu10.5 on Ubuntu.  
  
I have three tables/models and I want to do a find on Reservations ordering the results by the names in Sitetype.&nbsp; Sql for tables and the models look like the following:  
  
[sql]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [models]

```
CREATE TABLE `reservations` ( 
class Reservation < ActiveRecord::Base
  `id` int(11) NOT NULL AUTO_INCREMENT, belongs_to :space
  `space_id` int(11) NOT NULL default '0', end
  ...
  PRIMARY KEY (`id`)
);

CREATE TABLE `spaces` ( class Space \<  
ActiveRecord::Base  
`id` int(11) NOT NULL auto\_increment, belongs\_to  
:sitetype  
`name` varchar(24) NOT NULL default ‘’, has\_many  
:reservations  
`sitetype_id` mediumint(9) NOT NULL default ‘0’, end  
…  
&nbsp;PRIMARY KEY (`id`)  
);

CREATE TABLE `sitetypes` ( class Sitetype \<  
ActiveRecord::Base  
`id` int(11) NOT NULL auto\_increment, has\_many :spaces  
`name` varchar(12) NOT NULL default ‘’, has\_many  
:reservations, :through =\> :spaces  
PRIMARY KEY (`id`) end  
);

I have tried a number of things using includes of spaces and sitetypes  
but I have never been able to get the desired result. I can easily get  
it ordered by space.name using  
r=Reservation.find(:all, :include =\> “space”, :order =\>  
“spaces.name asc”)  
Whenever I try to include sitetype as in  
r=Reservation.find(:all, :include =\> [:space, :sitetype], :order  
=\> “sitetypes.name asc”)  
I get a complaint “Association named ‘sitetype’ was not found”

Could someone who understands this better than I recommend an approach  
to do what is needed here?

Norm
```

–   
  
You received this message because you are subscribed to the Google  
Groups “Ruby on Rails: Talk” group.  
  
To post to this group, send email to  
removed\_email\_address@domain.invalid.  
  
To unsubscribe from this group, send email to  
removed\_email\_address@domain.invalid.

For more options, visit this group at  
[http://groups.google.com/group/rubyonrails-talk?hl=en.](http://groups.google.com/group/rubyonrails-talk?hl=en.)

---

<div class="post-metadata">

**Author:** ![Norm\_S](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/9f8e36/32.png) [@Norm\_S](https://www.ruby-forum.com/u/Norm_S)\
**Post date:** [July 11, 2010, 9:35am UTC](https://www.ruby-forum.com/t/active-record-relationship-problems/191377/2 "2010-07-11T09:35:08Z")

</div>

Norm S. wrote:

> 

HTML mail to a mailing list. Yuck!

> I have three tables/models and I want to do a find on Reservations  
> ordering the results by the names in Sitetype. Sql for tables and  
> the models look like the following:

You might have a better mental model if you use ActiveRecord modeling  
instead of raw SQL.

class Reservation \< ActiveRecord::Base  
belongs\_to :space  
end

class Space \< ActiveRecord::Base  
belongs\_to :sitetype  
end

class Sitetype \< ActiveRecord::Base  
end

> Whenever I try to include sitetype as in  
> r=Reservation.find(:all, :include =\> [:space, :sitetype], :order =\> “sitetypes.name asc”)  
> I get a complaint “Association named ‘sitetype’ was not found”
> 
> Could someone who understands this better than I recommend an  
> approach to do what is needed here?

I think you have a nested datastructure and so need to use that same  
nesting when refering to it in the include.

Reservation.find(:all, :include =\> { :space =\> { :sitetype =\> {} } },  
:order =\> “sitetypes.name asc”)

Which I think can be reduced to this:

Reservation.find(:all, :include =\> { :space =\> :sitetype }, :order =\>  
“sitetypes.name asc”)

Note that I didn’t test this and so could have it wrong.

See the “Eager loading of associations” in this reference for more  
details:

[http://rails.rubyonrails.org/classes/ActiveRecord/Associations/ClassMethods.html](http://rails.rubyonrails.org/classes/ActiveRecord/Associations/ClassMethods.html)

Bob

---

<div class="post-metadata">

**Author:** ![Norm\_S](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/9f8e36/32.png) [@Norm\_S](https://www.ruby-forum.com/u/Norm_S)\
**Post date:** [July 11, 2010, 5:26pm UTC](https://www.ruby-forum.com/t/active-record-relationship-problems/191377/3 "2010-07-11T17:26:53Z")

</div>

Bob P. wrote:

> ```
> Norm S. wrote:
> ```
> 
> > ```
> > <html>
> > ```
> 
> ```
> HTML mail to a mailing list. Yuck!
> ```

Sorry about that.&nbsp; I wasn't thinking.  

> ```
> 
> ```
> 
> > ```
> > I have three tables/models and I want to do a find on 
> > Reservations
> > ordering the results by the names in Sitetype. Sql for tables and
> > the models look like the following:
> > ```
> 
> ```
> You might have a better mental model if you use ActiveRecord modeling
> instead of raw SQL.
> ```

I gave you both the sql and the models just to demonstrate that the database was correctly formed.  

> ```
> class Reservation < ActiveRecord::Base
> belongs_to :space
> end
> 
> class Space \< ActiveRecord::Base  
> belongs\_to :sitetype  
> end
> 
> class Sitetype \< ActiveRecord::Base  
> end
> ```
> 
> > ```
> > Whenever I try to include sitetype as in
> > r=Reservation.find(:all, :include => [:space, :sitetype], :order 
> > => "sitetypes.name asc")
> > I get a complaint "Association named 'sitetype' was not found"
> > 
> > Could someone who understands this better than I recommend an  
> > approach to do what is needed here?
> > ```
> 
> ```
> I think you have a nested datastructure and so need to use that same
> nesting when refering to it in the include.
> 
> Reservation.find(:all, :include =\> { :space =\> { :sitetype =\>  
> {} } }, :order =\> “sitetypes.name asc”)
> 
> Which I think can be reduced to this:
> 
> Reservation.find(:all, :include =\> { :space =\> :sitetype },  
> :order =\> “sitetypes.name asc”)
> ```

That works like a charm! &nbsp; I do not understand the :include =\> { :space =\> :sitetype } syntax.&nbsp; What is that saying and why does it work?

> ```
> Note that I didn't test this and so could have it wrong.
> 
> See the “Eager loading of associations” in this reference for more  
> details:
> 
> [http://rails.rubyonrails.org/classes/ActiveRecord/Associations/ClassMethods.html](http://rails.rubyonrails.org/classes/ActiveRecord/Associations/ClassMethods.html)
> ```

I looked at eager loading in Agile Web D. and did not really understand it all (obviously).&nbsp; I will look again at the api as recommended to see if that is any more understandable.  

> ```
> Bob
> ```

Thanks for the help.  
  
Norm  

–   
  
You received this message because you are subscribed to the Google  
Groups “Ruby on Rails: Talk” group.  
  
To post to this group, send email to  
removed\_email\_address@domain.invalid.  
  
To unsubscribe from this group, send email to  
removed\_email\_address@domain.invalid.

For more options, visit this group at  
[http://groups.google.com/group/rubyonrails-talk?hl=en.](http://groups.google.com/group/rubyonrails-talk?hl=en.)

---

<div class="post-metadata">

**Author:** ![Norm\_S](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/9f8e36/32.png) [@Norm\_S](https://www.ruby-forum.com/u/Norm_S)\
**Post date:** [July 11, 2010, 10:58pm UTC](https://www.ruby-forum.com/t/active-record-relationship-problems/191377/4 "2010-07-11T22:58:46Z")

</div>

Bob P. wrote:

> …  
> I don’t think I can do a better job than the above. But I can add a  
> few hints. Let’s work with AAA, BBB, CCC where AAA belongs\_to BBB and  
> BBB belongs\_to CCC.
> 
> …  
> Hope that helps!
> 
> Bob

That does help. Thanks for the detailed explanation.

Norm

---

<div class="post-metadata">

**Author:** ![Norm\_S](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/9f8e36/32.png) [@Norm\_S](https://www.ruby-forum.com/u/Norm_S)\
**Post date:** [July 11, 2010, 9:48pm UTC](https://www.ruby-forum.com/t/active-record-relationship-problems/191377/5 "2010-07-11T21:48:31Z")

</div>

Norm S. wrote:

> > HTML mail to a mailing list. Yuck!
> 
> Sorry about that. I wasn't thinking.

Please, please, please, try something different. You are still  
sending HTML email to the mailing list.

> > You might have a better mental model if you use ActiveRecord modeling  
> > instead of raw SQL.
> 
> I gave you both the sql and the models just to demonstrate that the  
> database was correctly formed.

Probably an effect of the HTML being munged along the way somewhere.  
I couldn’t see it in the message. I only saw the SQL and generated  
the models from it. That is why I mentioned it. Because as far as I  
could see you only included the SQL. If you had sent your message as  
plain text there would have been less chance of format munging and  
then things would have been more clear.

> > I think you have a nested datastructure and so need to use that same  
> > nesting when refering to it in the include.  
> > Reservation.find(:all, :include =\> { :space =\> { :sitetype =\> {} } }, :order =\> “sitetypes.name asc”)  
> > Which I think can be reduced to this:  
> > Reservation.find(:all, :include =\> { :space =\> :sitetype }, :order =\> “sitetypes.name asc”)
> 
> That works like a charm!

Oh good!

> I do not understand the :include =\> { :space =\> :sitetype } syntax.  
> What is that saying and why does it work?
> 
> > See the “Eager loading of associations” in this reference for more details:  
> > [http://rails.rubyonrails.org/classes/ActiveRecord/Associations/ClassMethods.html](http://rails.rubyonrails.org/classes/ActiveRecord/Associations/ClassMethods.html)  
> > I looked at eager loading in Agile Web D. and did not really  
> > understand it all (obviously). I will look again at the api as  
> > recommended to see if that is any more understandable.

I don’t think I can do a better job than the above. But I can add a  
few hints. Let’s work with AAA, BBB, CCC where AAA belongs\_to BBB and  
BBB belongs\_to CCC.

If you say to AAA :include =\> [:bbb, :ccc] it puts BBB and CCC at  
the same level. It is an array and they are siblings. But they are  
not. The above would probably generate this SQL:

SELECT \* FROM aaa  
LEFT OUTER JOIN bbb  
ON aaa.id = bbb.aaa\_id  
LEFT OUTER JOIN ccc  
ON aaa.id = ccc.aaa\_id \<== Problem Here!

Since there isn’t a field aaa\_id in ccc that fails.

Since the relationship is AAA belongs\_to BBB and BBB belongs\_to CCC  
this means that BBB and CCC are at different levels in the hierarchy.  
So instead your eager loading must say that same hierarchy. In AAA it  
must be:

:include =\> { :bbb =\> { :ccc =\> {} }}

Because of the hierarchy of hashes ActiveRecord knows to chain  
together the same hiearchy through the SQL. That will probably  
generate something like this SQL:

SELECT \* FROM aaa  
LEFT OUTER JOIN bbb  
ON aaa.id = bbb.aaa\_id  
LEFT OUTER JOIN ccc  
ON bbb.id = ccc.bbb\_id \<== Okay now

The hiearchy of hashes in the :include say that BBB points to CCC, not  
AAA pointing to CCC. And they say that CCC isn’t pointing anywhere.

[Disclaimer: I really hope I got the above right! And remember that  
SQL generation is often very version specific. Different versions  
will optimize the generated SQL differently. At a given time the  
above was probably right but I expect it to be quite different in  
different versions.]

And then because CCC’s hash is empty it can be reduced to nothing.  
ActiveRecord will look at the value and it will either be a symbol or  
a hash. If treats the symbol as a special empty hash in order to make  
that reduction possible. It is just one of the many shortcuts  
available.

:include =\> { :bbb =\> :ccc }

The long form is really the more correct form. I cited it because I  
think that is the one to understand and then the translation is there  
to the short form as a shortcut. But shortcuts are often more  
difficult to understand because of what they leave out.

Hope that helps!

Bob