Active record relationship problems

Rails
1
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.  Sql for tables and the models look like the following:

[sql]                                                                                     [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

…

 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
[email protected].

To unsubscribe from this group, send email to
[email protected].

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

2

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

Bob

3
Bob P. wrote: 
Norm S. wrote:
  
<html>
    
HTML mail to a mailing list.  Yuck!
Sorry about that.  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!   I do not understand the :include => { :space => :sitetype } syntax.  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

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.
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
[email protected].

To unsubscribe from this group, send email to
[email protected].

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

4

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

5

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