Problem with creating a database scheme/model

Hi there,

I have a problem with creating a database scheme and the according
models.
To describe the situation I made small scribble, because I think that
explains it best.

http://herrbuerger.com/scheme.001.jpg

So I have several pages and each page has many texts, pictures and
assets. So far the models look like this:

Page
has_many :texts
has_many :pictures
has_many :assets

Texts
belongs_to :page

Picture
belongs_to :page

Asset
belongs_to :page

Right now i have to query each table (pictures, texts, assets)
seperately to get all the stuff that belongs to ONE page.

What I want to do is just fire one query to get all the items. I
really rack my brain on this one.
Hopefully one of you guys can give me a hint on how to do this.

Thanks for your time,
Chris

(note: I posted this to the other Rails Mailinglist, because I did not
know about this one. However this list seems to be the more active
one.)

If I understand you correctly: Personally I'd do a find_by_sql with a
couple of joins in but I'm told you can use the acts_as_ferret plugin
too

Chris-Aix wrote:

Page
has_many :texts
has_many :pictures
has_many :assets

Texts
belongs_to :page

Picture
belongs_to :page

Asset
belongs_to :page

Right now i have to query each table (pictures, texts, assets)
seperately to get all the stuff that belongs to ONE page.

What I want to do is just fire one query to get all the items. I
really rack my brain on this one.
Hopefully one of you guys can give me a hint on how to do this.

p = Page.find :first
p.texts, p.pictures, p.assets

If you mean that rails has to make all the queries then search on eager
loading in the rails api

Thanks for your tips.

The first thing that came to my mind was find_by_sql, but I guess that
the queries are not that easy.
And, Ilan, the first solution that you recommended is like the one I
use now.

And to specify my problem a little bit more I'll try to explain it a
little better.
Right now the (pseudo)code on the "page" looks like that:
<page>
get_all_texts
get_all_pictures
get_all_assets
</page>

The thing is that these elements should come out ordered, so it look
for example like this:
<page>
text_1
picture_2
asset_1
picture_1
</page>

I hope that explains it a little bit better.

ok. i read the stuff about eager loading and it helped me out a little
bit.

right now i am doing something like this:
page = Page.find(:first, :include => [:pictures,:assets, :texts])

so this query gets all the results i need. the problem is each of this
tables has a position field and the endresult should be ordered this
way.
i made another scribble to explain it better.

http://herrbuerger.com/scribble_list.001.jpg

any ideas?

Chris-Aix wrote:

ok. i read the stuff about eager loading and it helped me out a little
bit.

right now i am doing something like this:
page = Page.find(:first, :include => [:pictures,:assets, :texts])

so this query gets all the results i need. the problem is each of this
tables has a position field and the endresult should be ordered this
way.
i made another scribble to explain it better.

http://herrbuerger.com/scribble_list.001.jpg

any ideas?

irb(main):001:0> elements = []
=> []
irb(main):002:0> 10.times {|n| elements << %w{text picture
asset}.sort_by {rand}.shift + "_" + n.to_s}
=> 10
irb(main):003:0> elements.sort_by {rand}
=> ["picture_7", "asset_5", "text_3", "text_6", "picture_0",
"picture_8", "text_2", "text_9", "picture_4", "text_1"]
irb(main):004:0> elements.sort_by {|a| a.match(/_(\d+)$/)[1].to_i}
=> ["picture_0", "text_1", "text_2", "text_3", "picture_4", "asset_5",
"text_6", "picture_7", "picture_8", "text_9"]