> Subject: [Rails] List views: avoiding n+1 selects
> To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> Message-ID:
<200504190110.21247.michael-q5aiKMLteq4b1SvskN2V4Q@public.gmane.org>
> Content-Type: text/plain; charset="us-ascii"
>
> In a list view I want to display data from the listed objects proper and
> also data from a to-one associated object. To make it a bit more
> concrete, I do a find like this
>
> q=<<END
> select p.*, e.lastname, e.firstname
> from projects p, employees e
> where p.leader_id = e.id
> order by e.lastname, e.firstname
> END
>
> Project.find_by_sql(q)
>
> The purpose of doing it like this is to get all data in a single select,
> instead of wasting n selects on retrieving the employee data. lastname
> and firstname are added as extra attributes to Project instances. Thus,
> in a view I can access them like this
>
> <% for project in @projects %>
> <%= project.lastname %>, <%= project.firstname %>
> <% end %>
>
> Which is not very elegant as the view here is coupled to the
> implementation detail of how the objects are retrieved from the
> database. Instead, I''d rather write
>
> <% for project in @projects %>
> <%= project.leader_name %>
> <% end %>
>
> But how to implement that method? Here''s a first stab
>
> class Project
> def leader_name
> lastname + '', '' + firstname
> end
> end
>
> Now, this method works in the special case that the Project instance has
> been created with the additional employee attributes. If not, it fails.
> What I really want, but don''t see how to get is, in Pseudo Ruby
>
> class Project
> def leader_name
> if project_leader_has_been_loaded || !has_project_leader_attribs
> project_leader.name
> else
> lastname + '', '' + firstname
> end
> end
>
> has_project_leader_attribs can be implemented in terms of
> ActiveRecord::Base#attribute_present?. But, for
> project_leader_has_been_loaded, how can I find out whether an
> associated object has already been created?
>
> Michael
>
> --
> Michael Schuerig The more it stays the same,
> mailto:michael-q5aiKMLteq4b1SvskN2V4Q@public.gmane.org
The less it changes!
> http://www.schuerig.de/michael/ --Spinal Tap, The Majesty of Rock
Why not forget about doing this sort of data manipulation in Ruby, and
use a view instead?
Your SQL statement would then become something like:
SELECT PROJECTNAME,LEADER_FIRSTNAME, LEADER_LASTNAME, LEADER_FULLNAME
FROM PROJECT_LEADERS
and could return a table something like
"Project 1", "John", "Smith", "John
Smith"
...
if you really need to have first names and last names separated out.
You''re dealing with one view rather than 2 tables straightaway, so
your RoR code is immediately *much* simpler. If you need to
insert/update/delete data, you or your DBA can create one or more
stored procedures to do it - check
http://wiki.rubyonrails.com/rails/show/HowtoUsePostgresViewsAsTables
as an example of how to do it in Postgres. It can be done in Oracle,
DB2, SQL Server, ... as well, using a similar approach.
Doing the type of data manipulation you''re doing in Ruby generally
gets DBAs upset. Most competent DBAs will tell you to do as much of
your data access as possible using stored procedures or views rather
than SELECT/INSERT/DELETE/UPDATE statements; using a few, standard
interfaces to the database lets DBAs tune the performance of the
database much better than trying to cope with every ad-hoc data
request than any user could come up with. In fact, in my experience
most DBAs won''t give you access to SELECT/INSERT/DELETE/UPDATE on
production databases; the risk of someone creating a massive SELECT
with 15 JOINS that locks up the database for minutes at a time, or a
dumb DELETE or UPDATE, are too great.
As an app developer, you want to abstract the way the database
internally formats data from your code as much as possible. In your
case, you shouldn''t need to know that there''s 2 tables called
PROJECTS
and EMPLOYEES, and that there''s a foreign key called LEADER_ID in
PROJECTS that references EMPLOYEES. Instead, you should know there''s
a view that has fields e.g. PROJECTNAME, LEADER_FIRSTNAME,
LEADER_LASTNAME and is already sorted appropriately. That way, if
requirements change and your DBA has to add new fields or re-normalise
the database, you won''t then have to go back and fix all your
now-broken SQL in this and any other application you''re supporting to
support the new database structure. Instead, you just get the DBA to
fix any broken views and stored procedures your apps use to access
data, so they work as they did before the database was changed. This
is generally pretty easy for a competent DBA.
Regards
Dave M.