Using find results the DNY way

rubyonrails-talk
1

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.

2

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

3

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

4

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?

5

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

Fred

6

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.

7

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.

8

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.

9

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.

10

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

11

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.

12

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

13

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.

14

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

15

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.

16

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.

17

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

18

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.

19

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,

20

> 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