ror:activerecord - Solin Expertise Center

Trace: • using_soda_to_create_new_moodle_modules • web_statistics • installing_and_configuring • control_panels • bind • desktop • wordpress • forms • virtualization • colorbox_in_course_format
This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== ActiveRecord ======




===== Nested :include vs. Home Made Joins =====

(Using Rails 1.1.6 and Ruby ruby 1.8.5 (2006-08-25) [i486-linux])

To avoid multiple database queries, you can use the ''**:include**''. Here's an example:

<code>
    @saved_games = SavedGame.find(
          :all,
          :include => [:account]
        ) 
</code>

This code will retrieve all saved_game and the associated account objects (you have to configure your models accordingly).

But what happens if the account model references yet another model? Suppose accounts belong to different organizations. If you use this code in your view:

<code>
   <% for saved_game in @saved_games %>
     Organization: <%= saved_game.account.organization.name %>
   <% end %>
</code>

Then you'll end up with as many database queries as there are organizations.







==== Home Made Joins ====

To retrieve all required information in one big query, you can do this:

<code>
    @saved_games = SavedGame.find(
          :all,
          :limit => 100,
          :select => "DISTINCT *",
          :joins => " INNER JOIN ( 
            (people INNER JOIN organizations ON people.organization_id = organizations.id)
              INNER JOIN accounts ON accounts.person_id = people.id
            ) ON saved_games.account_id = accounts.id",
          :order => 'saved_games.score DESC',
          :conditions => ['saved_games.scheduled_exercise_id IN (SELECT id FROM scheduled_exercises WHERE 1 = 1)']
        )
</code>

(This example is even more complex than the one above, because accounts now belong to people, who in turn belong to organizations).

This endows each of the saved_game objects in @saved_games with additional attributes, derived from the joined tables. Note that the saved_game objects are not writable now. Of course, that doesn't matter if you're just outputting a list. For example:

<code>
  <% for saved_game in @saved_games %>
    <tr>      
      <td valign="top"><%= saved_game.score.to_s %></td>
      <td valign="top"><%= saved_game.first_name + " " + saved_game.last_name %><br/><%= saved_game.name %></td>
      <td valign="top"><%= saved_game.scoredate.strftime("%d-%m-%Y %H:%M") %></td>
    </tr>  
  <% end %>
</code>

Here, the attributes ''name'', ''first_name'' and ''last_name'' are actually derived from the organizations and the people tables respectively.





==== Nested :include ====

It turns out that there is another way, documented at [[http://snippets.dzone.com/posts/show/2089|DZone Snippets]], [[http://blog.chrispcritter.com/xml/atom/article/14/feed.xml|this blog]] and in an IBM article titled [[http://www.ibm.com/developerworks/web/library/wa-rails3/|Optimizing ActiveRecord]] (see listing 9). You can simply nest the included associations:

<code>
    @saved_games = SavedGame.find(
          :all,
          :limit => 100,
          :include => [:account => {:person => :organization}],
          :order => 'saved_games.score DESC',
          :conditions => ['saved_games.scheduled_exercise_id IN (SELECT id FROM scheduled_exercises WHERE 1 = 1)']
        )
</code>

This code retrieves the same information as the query above, only now it **is** writable! And the code is more readable.

Here is the view:

<code>
  <% for saved_game in @saved_games %>
    <tr>      
      <td valign="top"><%= saved_game.score.to_s %></td>
      <td valign="top"><%= saved_game.account.person.first_name + " " + saved_game.account.person.last_name %><br/><%= saved_game.account.person.organization.name %></td>
      <td valign="top"><%= saved_game.scoredate.strftime("%d-%m-%Y %H:%M") %></td>    </tr>  
  <% end %>
</code>









==== Side by Side Comparison ====

Nested includes yield writable objects and readable code, so surely we should always use this method, right? Well, it turns out that there are some performance differences. Here are the results of a little test, comparing the rendering speed of the views. I have reloaded the view three times for each method and I took the figures from the development log (I restarted the WEBrick server in between each reload).

Home Made Joins
^ Query Load Time ^ Rendering Speed ^
| 0.000265 | 0.01054 |
| 0.000273 | 0.08042 |
| 0.000260 | 0.07940 |


Nested :include
^ Query Load Time ^ Rendering Speed ^
| 0.000286 | 0.11831 |
| 0.000288 | 0.11892 |
| 0.000277 | 0.11321 |


Conclusion: there aren't any really significant differences in the querying speed, but the simple attributes resulting from the Home Made Joins method are rendered much faster!



===== Results of Find Method Do NOT End Up in a Proper Array =====

I ran into a little problem with the results of an ActiveRecord call to find. This problem is also documented [[http://blade.nagaokaut.ac.jp/cgi-bin/scat.rb/ruby/ruby-talk/236171|here]]. The results of a call to MyModelClass.find are supposed to end up in an array. But if you try to use the "find" method of the array itself (actually mixed in by the Enumerables module), you'll end up with an error. Example:

<code>
my_model_objects = MyModelClass.find(:all)
my_model_objects.find {|obj| obj.some_property == x}
</code>

Results in:

<code>
ActiveRecord::RecordNotFound: Couldn't find MyModelClass without an ID
</code>

It turns out that ActiveRecord uses a slightly modified version of the Array object. To 'find' elements in the results array, use the ''**detect**'' method, to 'find_all' elements, use ''**select**''.



===== Clear an Association Collection without Saving or Updating =====

Association collections, or arrays, are usually emptied like this:

<code>
@author.articles.clear
</code>

An alternative is this:

<code>
@author.articles = []
</code>

Both method generate an sql UPDATE statement which is immediately executed and results in the destruction of the association table records. The end result is that the collection array is indeed emptied. But the database has also been altered. What if we don't want that?

We have to revert to a dirty hack. You cannot use Ruby's innate Array#delete or Array#clear methods, because they've been overwritten in Rails' ActiveRecord. So here's the trick: find an Array method that has not been overwritten.

<code>
@author.articles.delete_if {|a| 1 == 1}
</code>

This works fast enough for small collections.
You are here: start » ror » activerecord
Views

Navigation

Personal Tools

Search

Toolbox
