ActiveRecord

(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:

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

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:

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

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

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

    @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)']
        )

(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:

  <% 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 %>

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

It turns out that there is another way, documented at DZone Snippets, this blog and in an IBM article titled Optimizing ActiveRecord (see listing 9). You can simply nest the included associations:

    @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)']
        )

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:

  <% 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 %>

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

Nested :include

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!

I ran into a little problem with the results of an ActiveRecord call to find. This problem is also documented 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:

my_model_objects = MyModelClass.find(:all)
my_model_objects.find {|obj| obj.some_property == x}

Results in:

ActiveRecord::RecordNotFound: Couldn't find MyModelClass without an ID

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.

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

@author.articles.clear

An alternative is this:

@author.articles = []

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.

@author.articles.delete_if {|a| 1 == 1}

This works fast enough for small collections.