Reduce transactions required for columns in other tables?

rubyonrails-talk
1

I have a simple application that keeps track of projects and tasks:

class Project < ActiveRecord::Base   has_many :Tasks end

class Task < ActiveRecord::Base   belongs_to :Project end

In a list view of tasks, I want to show the project name (and link it to the action allowing the user to edit a project). Currently, that logic is in the view (which may be my first mistake):

from app/view/task/list.rhtml:

<td><%= link_to Project.find(task.project_id).name, :controller => 'Project', :action => 'edit', :id => task.project_id %></td>

But this causes the application to make two calls to the database to get this information: one when the controller pulls its list of tasks:

from app/controller/task_controller.rb: def list   @task_pages, @tasks = paginate :tasks, :per_page => 10 end

and once when the Project.find() call is executed while parsing the view. It would be far more efficient, from a database perspective, if I only had to call on the database once to get the list of tasks. Probably I could rewrite TaskController.list() to use Task.find() with the best way to handle this situation? If so, then how do I paginate the results?

Or is it better to just suck it up, and let Rails make an extra call to the database for every record returned? It's not so bad when there are fewer than 10 simultaneous users, and pagination keeps it to 10 tasks per page -- but that's still 11 database transactions per page when it could just be 1.

Thoughts?

2

You need to move the find code out of your view and use eager loading in your controller. Try this:

<td><%= link_to task.project.name, :controller =>'Project', :action => 'edit', :id => task.project %></td>

def list     @task_pages, @tasks = paginate :tasks, :include => :project, :per_page => 10 end

-Bill

Z wrote:

3

That seems to have done the trick -- although I had to change the case of "project" to "Project" to get it to work. Have I fouled up on a case convention?

My database tables (MySQL on ext3, INNODB engine) are tasks and projects. Models are Task and Project.

Maybe I'm being too nitpicky ... but I'm trying to learn and follow the conventions.

Scott

4

I have a simple application that keeps track of projects and tasks:

class Project < ActiveRecord::Base   has_many :Tasks

has_many :tasks # note the lowercase

end

class Task < ActiveRecord::Base   belongs_to :Project

belongs_to :project # again, lowercase

end

In a list view of tasks, I want to show the project name (and link it to the action allowing the user to edit a project). Currently, that logic is in the view (which may be my first mistake):

from app/view/task/list.rhtml:

<td><%= link_to Project.find(task.project_id).name, :controller => 'Project', :action => 'edit', :id => task.project_id %></td>

link_to task.project.name, :controller => 'project', :action => 'edit', :id => task.project_id

or learn how to use named routes and say: link_to task.project.name, edit_project_path(task.project_id)

But this causes the application to make two calls to the database to get this information: one when the controller pulls its list of tasks:

from app/controller/task_controller.rb: def list   @task_pages, @tasks = paginate :tasks, :per_page => 10

This is the perfect example of eager loading to avoid the 1+N problem:

   @task_pages, @tasks = paginate :tasks, :include => :project, :per_page => 10

end

and once when the Project.find() call is executed while parsing the view. It would be far more efficient, from a database perspective, if I only had to call on the database once to get the list of tasks. Probably I could rewrite TaskController.list() to use Task.find() with a :join option (pulling projects.name from the database), but is that the best way to handle this situation? If so, then how do I paginate the results?

Or is it better to just suck it up, and let Rails make an extra call to the database for every record returned? It's not so bad when there are fewer than 10 simultaneous users, and pagination keeps it to 10 tasks per page -- but that's still 11 database transactions per page when it could just be 1.

Thoughts?

I think you need to spend a bit of time with a good book (like Agile Development with Rails, 2nd Ed.) or at least the online Rails API docs.

-Rob

Rob Biedenharn http://agileconsultingllc.com Rob@AgileConsultingLLC.com

5

Yes, in Ruby, anything starting with a capital letter is considered a constant. I noticed that in your classes, but I assumed that was just from you retyping it in your email. Your associations should be all lower case. You are not being too picky :slight_smile:

`class Task < ActiveRecord::Base

belongs_to :project

end

class Task < ActiveRecord::Base

belongs_to :Project

end`

-Bill

Z wrote: