Hi,
How would you order the results of a complex aggregation using 'calculate' (from ActiveRecord::Calculations::ClassMethod)?
I have a "legacy" table from which I would like to retrieve some aggregated data, with an ordering. Something like:
Entry.calculate('avg', 'thevalue', { :order=> 'avg_thevalue DESC' })
which generates the following (Postgres) query:
SELECT avg(subject) AS avg_subject FROM logevents ORDER BY avg_subject
This works, but the :order option depends on what I think are implementation details of the function calculate: namely the generation of the "AS avg_subject" alias in the query.
This becomes painful if I need more complex SQL expressions to get to the data (remember this is a legacy table :-)):
Entry.calculate('avg', 'CAST(SUBSTRING(thevalue, \'[0-9]+\.[0-9]+\') as double precision)' )
This generates:
SELECT avg(CAST(SUBSTRING(subject, '[0-9]+\.[0-9]+') as double precision)) AS avg_cast_substring_subject_0_9_0_9_as_double_precision FROM logevents
to sort the results, I would need to pass
{ :order => 'avg_cast_substring_subject_0_9_0_9_as_double_precision DESC' }
as an option to 'calculate'.
Something tells me that this is _not_ a good idea. The exact name of the alias seems database adapter dependent and it just looks ugly and fragile. It seems that this solution uses knowledge of implementation details, which is of course never a good idea, but I can't think of an alternative (besides sorting the results in Ruby instead of SQL).
Greetings,
Duco
