<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <head> </head> <body bgcolor="#ffffff" text="#000000"> <font size="-1"><font face="Helvetica, Arial, sans-serif">Using RoR 2.2.2 with MySql Server version: 5.0.75-0ubuntu10.5 on Ubuntu.<br> <br> 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:<br> </font></font><font size="-1"><tt><br> [sql] [models]<br> </tt></font> <pre wrap=""><tt>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 ... 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 ); </tt> 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 <tt>r=Reservation.find(:all, :include => "space", :order => "spaces.name asc")</tt> Whenever I try to include sitetype as in <tt>r=Reservation.find(:all, :include => [:space, :sitetype], :order => "sitetypes.name asc")</tt> 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 </pre> </body> </html> <p></p> -- <br /> You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.<br /> To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org<br /> To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com.<br /> For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.<br />
Norm Scherer wrote:> <html>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 Bob -- 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 rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1"
http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Bob Proulx wrote:
<blockquote
cite="mid:20100711073335.GA15005-gIqwFFuksC2PjiKwAfKZ4kEOCMrvLtNR@public.gmane.org"
type="cite">
<pre wrap="">Norm Scherer wrote:
</pre>
<blockquote type="cite">
<pre wrap=""><html>
</pre>
</blockquote>
<pre wrap=""><!---->
HTML mail to a mailing list. Yuck!
</pre>
</blockquote>
Sorry about that. I wasn''t thinking.<br>
<blockquote
cite="mid:20100711073335.GA15005-gIqwFFuksC2PjiKwAfKZ4kEOCMrvLtNR@public.gmane.org"
type="cite">
<pre wrap="">
</pre>
<blockquote type="cite">
<pre wrap="">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:
</pre>
</blockquote>
<pre wrap=""><!---->
You might have a better mental model if you use ActiveRecord modeling
instead of raw SQL.
</pre>
</blockquote>
I gave you both the sql and the models just to demonstrate that the
database was correctly formed.<br>
<blockquote
cite="mid:20100711073335.GA15005-gIqwFFuksC2PjiKwAfKZ4kEOCMrvLtNR@public.gmane.org"
type="cite">
<pre wrap="">
class Reservation < ActiveRecord::Base
belongs_to :space
end
class Space < ActiveRecord::Base
belongs_to :sitetype
end
class Sitetype < ActiveRecord::Base
end
</pre>
<blockquote type="cite">
<pre wrap="">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?
</pre>
</blockquote>
<pre wrap=""><!---->
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")
</pre>
</blockquote>
That works like a charm! I do not understand the :include => {
:space => :sitetype } syntax. What is that saying and why does
it
work?
<blockquote
cite="mid:20100711073335.GA15005-gIqwFFuksC2PjiKwAfKZ4kEOCMrvLtNR@public.gmane.org"
type="cite">
<pre wrap="">
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:
<a class="moz-txt-link-freetext"
href="http://rails.rubyonrails.org/classes/ActiveRecord/Associations/ClassMethods.html">http://rails.rubyonrails.org/classes/ActiveRecord/Associations/ClassMethods.html</a>
</pre>
</blockquote>
I looked at eager loading in Agile Web Development 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.<br>
<blockquote
cite="mid:20100711073335.GA15005-gIqwFFuksC2PjiKwAfKZ4kEOCMrvLtNR@public.gmane.org"
type="cite">
<pre wrap="">
Bob
</pre>
</blockquote>
Thanks for the help.<br>
<br>
Norm<br>
</body>
</html>
<p></p>
-- <br />
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.<br />
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org<br />
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org<br
/>
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.<br />
Norm Scherer wrote:> > HTML mail to a mailing list. Yuck! > <html> > 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 > > I looked at eager loading in Agile Web Development 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 -- 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 rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Bob Proulx 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 -- 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 rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.