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