Rails - May 2008 - order complex aggregation results in 'calculate'

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

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

On 27 May 2008, at 15:38, duco wrote:
>
> Hi,
> 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).
>
2 ideas:
1 - use column_alias_for to generate those column names (again,  
relying on implementation, but a little less so)
2 - no idea how portable sql wise this is, but in mysql at least you  
can say ''order by 2'' to order by the second column
> Greetings,
>
> Duco
>
> >

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

On May 27, 4:48 pm, Frederick Cheung
<frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
...
> 2 ideas:
> 1 - use column_alias_for to generate those column names (again,  
> relying on implementation, but a little less so)
> 2 - no idea how portable sql wise this is, but in mysql at least you  
> can say ''order by 2'' to order by the second column
>
> > Greetings,
>
> > Duco
2) is a nice idea,thanks! I checked it and indeed it is specified in
the SQL92 standard, so it seems portable enough. I also thought of 1,
but the problem is ''column_alias_for'' is a private method.

Duco
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---