There is a better technique to using find_by_sql for the total_budget method. The above example, while useful in that it shows a real world example of how an application was improved by adding some SQL magic, is also problematic. The issue is that it has forced the results of the SQL call into a collection of budget objects. This does not cause an error, but it would be neater if the objects returned using the SQL query formed a simple array of hashes whose properties were defined solely by the data returned from the database and did not inherit any of the properties from the parent model.

The solution is to use the parent’s connection to the database, and then use one of two connection methods to return the data in a more suitable set of object. The options are:

connection.select_all(sql)
#returns data in an array of hashes where each hash corresponds to a data row.
connection.select_one(sql)
#returns a single hash containing the first row of data from the sql call

Therefore, a better version of the total_budget method using the connection object, would be as follows:

def total_budget
sql = "SELECT SUM([value]) as total_budget
FROM [HL_Data_Warehouse].[dbo].[budgets] budget
INNER JOIN (
SELECT MAX([target_on]) as date
, [task_id]
FROM [HL_Data_Warehouse].[dbo].[budgets]
WHERE [project_id] = #{self.id} AND [part_of_parent] = 1
GROUP BY [task_id]
) AS last_target_on
ON last_target_on.date = budget.target_on
AND last_target_on.task_id = budget.task_id"
budget = connection.select_one(sql)
return budget.total_budget
end

GROUP BY is a very useful SQL option that can be used to quickly generate summary reports. It can be used via an ActiveRecord find method using the : group option. However, its use is not as simple as it might appear from the Rails api pages. Consider this example:

We have an application that stores data on the contents of boxes of fruit. It contains a single MySQL table:

We want a summary report that details how many of each type of fruits you have in all the boxes. We can use GROUP BY to do this. However, it is not as simple as the following:

report = Boxes.find(:all, :group => 'fruit')

This would generate the following:

This has not provided a summary, but rather has simply pulled out the first entry for each type of fruit.

So, what is missing?

Let us start by looking at the SQL that generated the group data:

SELECT * FROM boxes b GROUP BY fruit

The problem is that by using the short-hand * in the SELECT part of the statement, the job of selecting what is to be returned has been handed over to SQL. As the example above demonstrates, SQL's best effort will usually not give us the desired result. Instead, we must specify, which fields are to be included in the output. However, the following would generate an error:

SELECT fruit, quantity FROM boxes b GROUP BY fruit

We also need to specify an aggregate function for each field that is not being grouped. Here are some of the commonly used aggregate functions:

The field fruit is being grouped, so it can stay as it is in the SQL statement. An aggregate function needs to be applied to the quantity field, as this field is not being grouped. That is, there will be a number of quantity values in each group, but only one fruit value. So, all we need to tell SQL is, which quantity value should be returned for a group; the fruit value is automatically returned.

SELECT fruit, sum(quantity) AS 'total_quantity' FROM boxes b GROUP BY fruit

This will produce the desired result:

Notice that AS was needed to specify a meaningful name for the quantity output.

To generate the desired result set in Rails, the following code can be used.

report = Boxes.find(:all,
:select => "fruit, sum(quantity) AS 'total_quantity'",
:group => 'fruit')

By using the select option in the find statement, the output was controlled and the desired results obtained.

It is also possible to group on multiple fields. For example:

SELECT fruit,
source,
sum(quantity) as 'quantity',
count(*) as 'boxes'
FROM boxes b
GROUP BY fruit, source

This will produce a report showing how many fruits there are from each source and in how many boxes:

From this, we can see there are three boxes of Spanish oranges containing 310 fruits in total. The Rails code would look like this:

report = Boxes.find(:all,
:select => "fruit,
source,
sum(quantity) AS 'total_quantity',
count(*) as 'boxes'"
:group => 'fruit, source')

Simple, don't you think! Well not really.....

Aggregate functions need to be used carefully. Consider how you would find the source of the boxes with the least fruit of each type. Your first stab at a solution may be:

SELECT fruit,
MIN(quantity) AS 'quantity',
MIN(source) AS 'source'
FROM boxes
GROUP BY fruit

This would return the record set below, which at first glance looks correct:

However, the source of the box with the least apples is France and not Belgium. In fact, it is only by chance that the other two records match the correct result. MIN (source) has returned the first source in alphabetical order—not the source that matches the minimum quantity.

To get the correct result, we first need to find the minimum quantities for each fruit and then use that data to pull out the records that match that information. The following code does that:

SELECT b.fruit, b.quantity, b.source
FROM boxes b
JOIN (SELECT fruit, MIN(quantity) AS 'quantity'
FROM boxes
GROUP BY fruit
) AS s
ON b.fruit = s.fruit AND b.quantity = s.quantity

The result is shown below.

ActiveRecord's find method has a: joins option and can be used to construct this query. A: from option is also needed so that an alias of 'b' can be specified for the boxes table. However, the resulting code is more complex than the original SQL code:

report = Boxes.find(:all,
:select => 'b.fruit, b.quantity, b.source'
:from => 'boxes b'
:join => "(SELECT fruit, MIN(quantity) AS 'quantity'
FROM boxes
GROUP BY fruit
) AS s
ON b.fruit = s.fruit AND b.quantity = s.quantity)"

Personally, I would use a find_by_sql method as it would be easier to maintain:

sql = "SELECT b.fruit, b.quantity, b.source
FROM boxes b
JOIN (SELECT fruit, MIN(quantity) AS 'quantity'
FROM boxes
GROUP BY fruit
) AS s
ON b.fruit = s.fruit AND b.quantity = s.quantity"
report = Boxes.find_by_sql(sql)

Knowledge of SQL makes it easier to get the most of the: group option in ActiveRecord's finds method and also makes it easier to extend further SQL's GROUP BY options.