Rails - Oct 2005 - Using active record for SELECT MAX(column) FROM ...

Hi,

Is there an easy way of querying an active record for a maximum column 
value? I need to do queries like:

SELECT MAX(salary) FROM employees

TIA,

Jeroen

From memory:
max = Employee.find_by_sql (''SELECT MAX(salary) AS big_fish FROM 
employees '').big_fish

This might require a to_i somewhere...

Jeroen Houben wrote:
> Hi,
>
> Is there an easy way of querying an active record for a maximum column 
> value? I need to do queries like:
>
> SELECT MAX(salary) FROM employees
>
> TIA,
>
> Jeroen
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails

or a "better" one (for you, specifically). Note this is a destructive
(or
constructive!) approach, and will alter data upon execution. Darn Heisenburg
and his Uncertainty.

Employee.execute([''Update employees set salary = 1000000 where name =
?'',
''Jeroen Houben''])

max = Employee.find_by_sql([''SELECT salary FROM employees where name =
?'',
''Jeroen Houben'']).salary



On 10/17/05, Jos <jos-opYYzZorcyRWk0Htik3J/w@public.gmane.org>
wrote:
>
> From memory:
> max = Employee.find_by_sql (''SELECT MAX(salary) AS big_fish FROM
> employees '').big_fish
>
> This might require a to_i somewhere...
>
> Jeroen Houben wrote:
>
> > Hi,
> >
> > Is there an easy way of querying an active record for a maximum column
> > value? I need to do queries like:
> >
> > SELECT MAX(salary) FROM employees
> >
> > TIA,
> >
> > Jeroen
> > _______________________________________________
> > Rails mailing list
> > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> > http://lists.rubyonrails.org/mailman/listinfo/rails
>
>
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>


--
Brock Weaver
brockweaver-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org

/* you are not expected to understand this */


_______________________________________________
Rails mailing list
Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
http://lists.rubyonrails.org/mailman/listinfo/rails

Huh, guys, this is wrong.

2005/10/17, Brock Weaver <brockweaver@gmail.com>:
>  Employee.execute(['Update employees set salary = 1000000 where name =
?',
> 'Jeroen Houben'])
>
>  max = Employee.find_by_sql(['SELECT salary FROM employees where name =
?',
> 'Jeroen Houben']).salary
>
> On 10/17/05, Jos <jos@montjaux.com> wrote:
> > From memory:
> > max = Employee.find_by_sql ('SELECT MAX(salary) AS big_fish FROM
> > employees ').big_fish
value = Employee.connection.select_one('SELECT MAX(salary) AS value
FROM employees')
max_salary = value['value'].to_f # Needed, because all values are
returned as strings

Querying a simple scalar doesn't require access to a model object, so
no need to use the Employee model, except to get the connection.

See http://rubyurl.com/UOc [1] for more details.

Bye !
François

[1]
http://api.rubyonrails.com/classes/ActiveRecord/ConnectionAdapters/AbstractAdapter.html#M000553

_______________________________________________
Rails mailing list
Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
http://lists.rubyonrails.org/mailman/listinfo/rails

On Oct 17, 2005, at 8:43 AM, Jeroen Houben wrote:
> Is there an easy way of querying an active record for a maximum  
> column value? I need to do queries like:
>
> SELECT MAX(salary) FROM employees
Someone (a friend of Courtenay''s?) mentioned aggregates after  
RubyConf, so I got to thinking.  Here''s a plugin that lets you do  
nice things like Employee.max_salary and Quiz.stddev_score.  Take the  
long road once and you''ll keep DRY on the next journey.  That, or  
just upgrade to beta gems (1.0 release candidate) and drop this in  
your plugins dir:

$ mkdir -p vendor/plugins/aggregates/lib
$ echo "ActiveRecord::Base.send :include, Aggregates" > vendor/ 
plugins/aggregates/init.rb
$ cat > vendor/plugins/aggregates/lib/aggregates.rb

module Aggregates
   def self.included(base)
     super

     base.cattr_accessor(:aggregate_functions)
     base.aggregate_functions =  
[:sum, :count, :min, :max, :avg, :stddev, :variance]

     base.extend(ClassMethods)
     base.class_eval do
       alias_method :method_missing_without_aggregates, :method_missing
       alias_method :method_missing, :method_missing_with_aggregates
     end
   end

   module ClassMethods
     def aggregate(function_name, attribute_name, options = {})
       # Look up the column corresponding to this attribute.
       column = columns_hash[attribute_name.to_s]
       raise ArgumentError, "No column in #{table_name} for attribute  
#{attribute_name}" if column.nil?

       # Construct SQL query.
       sql = construct_finder_sql(options.merge(:select => "# 
{function_name}(#{connection.quote_column_name(column.name)})"))

       # Perform query and type-cast its result.
       column.type_cast(connection.select_value(sql))
     end

     private
       def method_missing_with_aggregates(method, *args, &block)
         case method.to_s
           when /^(#{aggregate_functions.join(''|'')})_(.*)$/
             aggregate($1, $2, args.last.is_a?(Hash) ? args.pop : {})
           else
             method_missing_without_aggregates(method, *args, &block)
         end
       end
   end
end


Now, in your app:

class Employee < ActiveRecord::Base
end

Employee.max_salary


Best,
jeremy


_______________________________________________
Rails mailing list
Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
http://lists.rubyonrails.org/mailman/listinfo/rails

Francois Beausoleil wrote:
> Huh, guys, this is wrong.
> 
> 2005/10/17, Brock Weaver
<brockweaver-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>:
> 
>> Employee.execute([''Update employees set salary = 1000000 where
name = ?'',
>>''Jeroen Houben''])
>>
>> max = Employee.find_by_sql([''SELECT salary FROM employees
where name = ?'',
>>''Jeroen Houben'']).salary
>>
>>On 10/17/05, Jos <jos-opYYzZorcyRWk0Htik3J/w@public.gmane.org>
wrote:
>>
>>>From memory:
>>>max = Employee.find_by_sql (''SELECT MAX(salary) AS big_fish
FROM
>>>employees '').big_fish
> 
> 
> value = Employee.connection.select_one(''SELECT MAX(salary) AS
value
> FROM employees'')
> max_salary = value[''value''].to_f # Needed, because all
values are
> returned as strings
Thanks Francois, this looks more like what I''m after.

It''d be even nicer if I could just do:
value = Employee.connection.select_single(''SELECT MAX(salary)FROM 
mployees'').to_f

I guess there''s no select_single (or similar) method that just selects
a
single column from a query directly, returning a single string not a 
hash? I didn''t see it in the API docs, perhaps an idea for a future 
version ;-)

Jeroen

> It''d be even nicer if I could just do:
> value = Employee.connection.select_single(''SELECT MAX(salary)FROM
> mployees'').to_f
>
> I guess there''s no select_single (or similar) method that just
selects a
> single column from a query directly, returning a single string not a
> hash? I didn''t see it in the API docs, perhaps an idea for a
future
> version ;-)
>
> Jeroen
How about select_value?

connection.select_value("SELECT MAX(salary) FROM employees").to_f

connection.select_values("SELECT salary, created_at FROM employees")


--
rick
http://techno-weenie.net

Rick Olson wrote:
>>It''d be even nicer if I could just do:
>>value = Employee.connection.select_single(''SELECT
MAX(salary)FROM
>>mployees'').to_f
>>
>>I guess there''s no select_single (or similar) method that just
selects a
>>single column from a query directly, returning a single string not a
>>hash? I didn''t see it in the API docs, perhaps an idea for a
future
>>version ;-)
>>
>>Jeroen
> 
> 
> How about select_value?
> 
> connection.select_value("SELECT MAX(salary) FROM employees").to_f
> 
> connection.select_values("SELECT salary, created_at FROM
employees")
Excellent, thanks!
So where would I find out more about these methods, I couldn''t find
them
  in the RoR API docs. Presumably this is a seperate Connection class or 
something similar, but where are the docs?

Sorry, I''m just starting with RoR..

Jeroen

Hi !

2005/10/18, Jeroen Houben <jeroen@terena.nl>:
> Rick Olson wrote:
> > How about select_value?
> >
> > connection.select_value("SELECT MAX(salary) FROM
employees").to_f
> >
> > connection.select_values("SELECT salary, created_at FROM
employees")
>
> Excellent, thanks!
> So where would I find out more about these methods, I couldn't find
them
>   in the RoR API docs. Presumably this is a seperate Connection class or
> something similar, but where are the docs?
I think Rick's working with the beta gems, which means this method's
not available in the 0.13.1 release.

Bye,
François

_______________________________________________
Rails mailing list
Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
http://lists.rubyonrails.org/mailman/listinfo/rails