Gathering SQL data with TempObject

Another place I use TempObject is to organise the data created from a custom SQL statement. I use custom SQL statements when I need to gather data from three or more tables or do complicated JOIN or CASE statements. It is usually much quicker to combine data using SQL than it is to use Ruby.

Here is an example of how I can use TempObject to gather SQL data:

class Ball < ActiveRecord
  def get_all_using_sql
    sql = <<EOF
SELECT *
FROM balls
EOF
  TempObject.create_from_array_of_hashes(
     connection.select_all(sql) )
  end
end

select_all returns an array of hashes. TempObject.create_from_array_of_hashes converts the array of hashed into an array of TempObjects. The code to output that code is then of the same format as that used to display the data returned by a ActiveRecord find call.

So you can use the same code to display data gathered by Ball.get_all_using_sql as you would use on the data gathered by Ball.find(:all).

So this will work for both:

<table>
  <tr>
    <th>Size</th>
    <th>Colour</th>
  </tr>
<% for ball in @balls %>
  <tr>
    <td><%= ball.size -%></td>
    <td><%= ball.colour -%></td>
  </tr>
<% end %>
</table>

The array returned contains instances of TempObject and not Ball. So Ball instance methods won’t be available. This means that often find_by_sql is a better option. The TempObject technique is an additional way of gathering data and not a replacement for find_by_sql.

Also it make little sense to use this technique to replace ActiveRecord’s find. I would never actually use the example used above as Ball.find(:all) is so much easier to use.

However, when creating reports, the item names often don’t correspond to normal attributes. For example, if you wanted to list all balls used by month you may actually want ball.january, ball.february etc. To do that is often much faster using SQL than Ruby, and then the TempObject technique becomes very useful.

I also find myself wanting to gather data for reports and that data doesn’t easily correspond to a single model class. I could create a new class just for that report, but it is simpler just to use TempObject and use a custom SQL statement to populate each object.