Using find results the DNY way

Hi,

I'm working on a large project that handles quite some AJAX and
database interactions in short periods of time.

I'll simplify the project to an example. Say we're building houses and
a housebuilding project consists of floors and rooms, then to load a
configurator to edit the room and floor structure we would want to
load a project, which contains a few floors and each floor will
contain a few rooms. I would want to load the floors in a project, but
also the rooms in the floor that has been set to active using the "a"
flag. The other rooms are loaded when another floor is activated.

The models are built in a way so that a project has_many floors and a
floor has_many rooms. A room belongs_to a floor and a floor belongs_to
a project.

I could do this:

def configure
  @project = Project.find(:first, :conditions => { :id => params
[:id] } )
  @floors = Floor.find(:all, :conditions => { :project_id =>
@project.id })
  @rooms = Room.find(:all, :conditions => { :floor_id => Floor.find
(:first, :conditions => { :project_id => @project.id, :a =>
true } ).id })
end

But here I'm executing the Floor.find method twice and I feel that's
not very DNY. Now I can think of two things to do. The first one is to
get the line out of @floors where "a" is true, but I can only think of
a for loop to do that and I feel there must be a simpler way. Is
there?

The second way is a way in which I would use more of the model's
features. So load the project and all its children (floors and rooms).
But I cannot find any documentation on that. I want this done the
Rails way and I really have a feeling that I could easily cut the
number of database requests. Either way would be fine.

Of course I mean DRY. I'm not sure why I said DNY .. ?

def configure
       @project = Project.find( params[:id], :include => { :floors => :rooms } )
       @floors = @project.floors
       @rooms = @froors.map { |f| f.rooms }.flatten
end

Okay, thanks. But this includes all the rooms in a project. In my
project that could mean up to 255 results while I only want five or
six. I only want the rooms in the floor with :a => true. How to
include this in this method?

And this descends alot more in my project. Say I want to have
furniture in one of the rooms. Is is as simple as adding @furniture =
@rooms.map { |r| r.furniture }.flatten ? Can I keep on descending this
way?

You could have an association with the condition a=true and include
that association

Fred

Okay, thanks. But this includes all the rooms in a project. In my
project that could mean up to 255 results while I only want five or
six. I only want the rooms in the floor with :a => true. How to
include this in this method?

def configure
      @project = Project.find( params[:id], :include => { :floors => :rooms } )
      @floors = @project.floors
      @rooms = Room.all( :conditions => { :floor_id =>
@floors.map(&:id), :a => params[:a] } )
end

And this descends alot more in my project. Say I want to have
furniture in one of the rooms. Is is as simple as adding @furniture =
@rooms.map { |r| r.furniture }.flatten ? Can I keep on descending this
way?

Yep. But you should read more about active_record associations and
eager loading.

def configure
@project = Project.find( params[:id], :include => { :floors => :rooms } )
@floors = @project.floors
@rooms = Room.all( :conditions => { :floor_id =>
@floors.map(&:id), :a => params[:a] } )
end

Thanks, this got me on the right track. I won't be able to get all my
data in one database request, but at least I reduced the number of
database requests by two.

Yep. But you should read more about active_record associations and
eager loading.

I will, thank you.

You could have an association with the condition a=true and include
that association

I'm not sure what you mean. If you mean I should only get the floor(s)
with a=true: that's not what I want. I also want to list all floors in
the configure view. I just want to load the rooms from the floor where
a=true.

Thank you very much, Maurício Linhares. Thanks to your finger pointing
me towards eager loading I now understand alot more about the way
Rails models can be used in your controller. I can load my
configurator using just one database interaction now! My models are
getting fatter and fatter and my controller are getting thinner and
thinner.

Thanks again.

You could easily have two associations: rooms and rooms_with_a. Eager
load rooms_with_a when you want those with a = true and use the other
association if not. Sounds like you actually want to load all the
rooms though, just pull out some of them separately, in which case you
could do

@project = Project.find( params[:id], :include => { :floors
=> :rooms } )
@floors = @project.floors
@rooms = @floors.collect {|f| f.rooms.select {|room| room.a?}.flatten

Fred

You could easily have two associations: rooms and rooms_with_a. Eager
load rooms_with_a when you want those with a = true and use the other
association if not. Sounds like you actually want to load all the
rooms though, just pull out some of them separately, in which case you
could do

@project = Project.find( params[:id], :include => { :floors
=> :rooms } )
@floors = @project.floors
@rooms = @floors.collect {|f| f.rooms.select {|room| room.a?}.flatten

Fred

Well, in @project you would get all the rooms from all the floors like
this. I don't want that. I really want to get only the things I need.
So what I did now is:

In the project model I added had_one :active_floor and in the floor
model I added has_one :active_room

In the controller I used:

@project = Project.find(:first, :conditions => { :id => params
[:id] }, :include => [:floors, :active_floor, {:active_floor
=> :active_room}, {:active_floor => :rooms} ] )
@floors = @project.floors
@active_floor = @project.active_floor
@active_room = @active_floor.active_room
@rooms = @active_floor.rooms

And that does exactly what I want it to do. There may be errors in the
method above though, as I wanted to stick to the example and had to
change some things around.

Just so you know, that does exactly the same number of queries as

@project = Project.find params[:id]
@floors = @project.floors
@active_floor = @project.active_floor
@active_room = @active_floor.active_room
@rooms = @active_floor.rooms

(and you could save 2 queries by doing

@active_floor = @floors.detect {|floor| floor.active?}
@active_room = @rooms.detect {|room| room.active? }

instead of using the association you added).

:include is mostly pointless if you only have one parent object

Fred

Just so you know, that does exactly the same number of queries as

@project = Project.find params[:id]
@floors = @project.floors
@active_floor = @project.active_floor
@active_room = @active_floor.active_room
@rooms = @active_floor.rooms

I made an assumption that every instance of the find method would
trigger a query. That's not true?

(and you could save 2 queries by doing

@active_floor = @floors.detect {|floor| floor.active?}
@active_room = @rooms.detect {|room| room.active? }

Great, that's useful! Thank you.

:include is mostly pointless if you only have one parent object

Could you expand on that in addition to my first question? I'm still
learning.

If you do Project.find(:all, :include => :rooms)

then it does

Project.find(:all)
Room.find(:all, :conditions => {:project_id =>
list_of_ids_of_projects}
and then wires everything up.

If you were to iterate over your project collection then you'd just
have the 2 queries instead of 1 + number of projects.

If you do

Project.find(123456, :include => :rooms)

then it will do
Project.find(123456)
Room.find(:all, :conditions => {:project_id => [123465]}

which is exactly the same thing it would do if you did

p = Project.find 123456
p.rooms...

Fred

I'd just like to play spoiler and remind you that optimizing for smallest number of queries is not the same as optimizing for execution speed. Doing everything the Rails way is absolutely the right thing to do, but crunching down your database accesses early in development by using Ruby is betting that a Ruby implementation of array folding will be faster than a correctly written SQL implementation. If your result set is small, then it won't matter. It you have tens of thousands of rows, get your SQL right. Just a thought.

I'd just like to play spoiler and remind you that optimizing for
smallest number of queries is not the same as optimizing for execution
speed. Doing everything the Rails way is absolutely the right thing to
do, but crunching down your database accesses early in development by
using Ruby is betting that a Ruby implementation of array folding will
be faster than a correctly written SQL implementation. If your result
set is small, then it won't matter. It you have tens of thousands of
rows, get your SQL right. Just a thought.

Thanks for your input.

I really feel stuffing Ruby with 255 rows (the application max) when
you only need five or six is not the way to go. Especially if that
request will probably be executed more than once every second. That's
why I brought this up right now, whem I'm still in the early stages of
development. Bare in mind that this, for me, is a learning process,
but at the end it will have to be done right. That's why your thoughts
on this are very valuable to me.

The reason why I don't want to do:
@project = Project.find(:params[:id])
@floors = @project.floors
@rooms = @floors.rooms

.. is simply because I don't need @floors.rooms. I need
@active_floor.rooms, as I will request the rooms on other floors when
another floor is set to active. I do, however, need the names of every
floor in a project.

I feel making one database request for everything I need is better
than making five, but I've now learned find doesn't work that way.
@floors = @project.floors triggers another database request. But
still, this has helped me alot, because my code is alot cleaner now.

And it's more complicated than that: while your database server can
definitely do some things a lot quicker than fetching the rows in ruby
and then doing stuff to them, it is a lot easier to scale out your
ruby stuff (add more servers running your mongrels or passenger) than
it is to do the same thing to your database server.

Fred

And it's more complicated than that: while your database server can
definitely do some things a lot quicker than fetching the rows in ruby
and then doing stuff to them, it is a lot easier to scale out your
ruby stuff (add more servers running your mongrels or passenger) than
it is to do the same thing to your database server.

Fred

I'm not sure I agree with that. I'm coming from the hosting business
and you will generally have to use database clusters on large-scaled
applications or web sites anyway. It would be great if it was just
about adding a HDD, but that's only possible if your load stays below
the threshold. Of course on very large scaled applications you would
use caching servers and stuff like that, but that's not relevant here.
In my opinion you would rather have your database servers do the heavy
work as much as possible. A database server is designed to do that.
Just use your resources wisely.

Jaap Haagmans wrote:
[...]

I feel

Programming is ideally about facts, not feelings. (How closely we
approach that ideal is another question...)

making one database request for everything I need is better
than making five, but I've now learned find doesn't work that way.

It can. Use :joins instead of :include.

Best,

> And it's more complicated than that: while your database server can
> definitely do some things a lot quicker than fetching the rows in ruby
> and then doing stuff to them, it is a lot easier to scale out your
> ruby stuff (add more servers running your mongrels or passenger) than
> it is to do the same thing to your database server.

> Fred

I'm not sure I agree with that. I'm coming from the hosting business
and you will generally have to use database clusters on large-scaled
applications or web sites anyway. It would be great if it was just
about adding a HDD, but that's only possible if your load stays below
the threshold.

Sure, you can set up multiple master stuff, shard your data, run
memcache servers, read from slaves etc. None of these are trivial
though, certainly less so in my experience than adding another server
running mongrels into your cluster (which requires 0 code changes).
Not saying either that doing everything in ruby is the right thing
either, just raising the point that it's not a black and white world.

Fred