views:

1102

answers:

3

This is an age-old question where given a table with attributes 'type', 'variety' and 'price', that you fetch the record with the minimum price for each type there is.

In SQL, we can do this by:

select f.type, f.variety, f.price   
from (  select type, min(price) as minprice from table group by type ) as x  
inner join table as f on f.type = x.type and f.price = x.minprice;`

We could perhaps imitate this by:

minprices = Table.minimum(:price, :group => type)  
result = []
minprices.each_pair do |t, p|  
   result << Table.find(:first, :conditions => ["type = ? and price = ?", t, p])
end

Is there a better implementation than this?

+1  A: 
Table.minimum(:price, :group => :type)

See http://api.rubyonrails.org/classes/ActiveRecord/Calculations/ClassMethods.html for more.

Avdi
A: 

You can use #find_by_sql, but this implies returning a model object, which might not be what you want.

If you want to go bare to the metal, you can also use #select_values:

data = ActiveRecord::Base.connection.select_values("
        SELECT f.type, f.variety, f.price
        FROM (SELECT type, MIN(price) AS minprice FROM table GROUP BY type ) AS x
        INNER JOIN table AS f ON f.type = x.type AND f.price = x.minprice")
puts data.inspect
[["type", "variety", 0.00]]

ActiveRecord is just a tool. You use it when it's convenient. When SQL does a better job, you use that.

François Beausoleil
A: 

François: Yes, I understand that ActiveRecord is just a tool and that I can always resort to SQL but since I'm guessing that this is a common problem, I'm curious if ActiveRecord have something to accommodate those kinds of queries.

Avdi: Problem with Table.minimum(:price, :group => :type) (which I've already mentioned in my question) is that it returns an OrderedHash with only the price and type attributes, and not the whole record. What I want is the Table instances with the records matching the conditions, with all the Table attributes.

It works a lot better if you contact answerers by leaving a comment on their answer than by leaving another answer. I look over my comments from time to time, but I saw this response purely by chance.
Avdi