Active record relationship problems

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

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

Bob Proulx wrote:

Norm Scherer 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:

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.

Bob

Thanks for the help.

Norm

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

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