Philip D. wrote:
The reason I ask is because if I load the Employees into a collection
inside the script/console, then the console echos the collection to the
screen and you can see all the objects and all the “has_many” objects.
At this point, the system loaded everything.
I’m not sure I understand what you are doing here. What is the
“has_many” objects you are referring to? You have an employee model:
class Employee < ActiveRecord::Base
end
If also said that you are trying to search based on a related object (in
your other email). So I assume you have something like:
class Employee < ActiveRecord::Base
has_many :tasks, :dependent => :destroy
end
class Task < ActiveRecord::Base
belongs_to :employee
end
Now you want to load all employees that have high priority tasks. So you
do:
@lazy_employees = Employee.find :all, :include => ‘tasks’,
:conditions => [‘tasks.priority = ?’, ‘high’]
Now your complaint is that the tasks that meet the conditions are loaded
with the above code which you feel is a bit wasteful? Am I understanding
your problem correctly?
If that is the case there are a few things you can do:
-
Make sure it really matters. Does that extra loading of data actually
cause a performance problems? If not don’t worry about it. The above
code is simple to understand. If it is an actual performance problem
then proceed.
-
You could switch from using :include to :joins. This allows you to
join without loading the actual related objects. So:
@lazy_employees = Employee.find :all,
:joins => %q{INNER JOIN tasks ON
tasks.employee_id = employees.id AND
tasks.priority = ‘high’}
You are removing the convienance of what :include provides (automatic
joining and automatic object loading of the related object) but you gain
more control. Your join is now a inner join instead of an outer join
which is much more efficient. Also you are not loading those related
objects.
- If you want to keep the loading of related objects (for example if
you use those objects in your code and want to avoid N+1 query problem)
you can also improve the first attempt using the :select option so only
the fields you actually use are loaded. For example:
@lazy_employees = Employee.find :all, :include => ‘tasks’,
:select => ‘employees.name, task.name, tasks.due_on’,
:conditions => [‘tasks.priority = ?’, ‘high’]
This limits you to only being able to print the employee name, task name
and due date but it saves on the amount of data that has to be loaded.
- If you don’t care about related objects and only want certain fields
you can combine step 2 and 3 to produce:
@lazy_employees = Employee.find :all,
:select => ‘employees.name’,
:joins => %q{INNER JOIN tasks ON
tasks.employee_id = employees.id AND
tasks.priority = ‘high’}
Now you are only loading the data you need, using an INNER JOIN to
reduce the load in SQL and not loading the related objects that you
don’t plan to print anything out about. But obviously now it is much
uglier. If you really need the performance try to push the code down to
the model level so your controllers and view remain easy to work with.
See Buckblog: Skinny Controller, Fat Model
for more info on this process.
In the end everything resolves to find_by_sql. Depending on your needs
you might be able to operate at a very high level like the first
attempt. Or you may need to operate almost at the SQL-level like the
last attempt. You always still have the option of operating completely
at sql if you need absolute control. Determining at what level to
operate at is determined by a balance of performance and code
readability. I have found in practice I rarely use actual SQL code. Most
my my queries look like:
Employee.find_by_last_name(‘Anderson’)
Employee.find :all, :include => :departments,
:conditions => [‘departments.name = ?’, ‘IT’]
But every app will have a few queries that get closer to SQL. I have
developed around 10 Rails applications thus far and never had a need for
find_by_sql although I don’t rule it out.
ActiveRecord is a thin wrapper around the relational model. There are
other ORMs that operate at a higher level although I find that they
often don’t give you the low-level access that you occasionally need. I
think Rails reaches a nice compromise of being a thin wrapper with some
high-level features (such as dynamic finders and :include), but also
providing complete low-level access.
Eric