StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I have 2 sulutions in your case. My database is mysql, and I simplify your code for demo, I think you can extend it. </p> <p>The first is Squeel way, I mixed "sift" in Squeel and "from" in ActiveRecord Query. I installed postgresql and tested my solution just now, It seems hardly to use "squeel" and "epoch from" together, but I found an alternative way in postgresql, it called "date_part". I also modified the sql and reduced the duplications of calculation: </p> <pre><code>class User < ActiveRecord::Base sifter :w_computed_metric do (id * 2).as(computed_metric) end sifter :w_age_in_seconds do (date_part('epoch' , now.func) - date_part('epoch', created_at)).as(age_in_seconds) end sifter :w_compound_computed_metric do (computed_metric / age_in_seconds).as(compound_computed_metric) end def self.subquery select{['*', sift(w_computed_metric) , sift(w_age_in_seconds)]} end def self.w_all_additional_metrics select{['*', sift(w_compound_computed_metric)]}.from("(#{subquery.to_sql}) users") end end </code></pre> <p>It produced the sql:</p> <pre><code>SELECT *, "users"."computed_metric" / "users"."age_in_seconds" AS compound_computed_metric FROM (SELECT *, "users"."id" * 2 AS computed_metric, date_part('epoch', now()) - date_part('epoch', "users"."created_at") AS age_in_seconds FROM "users" ) users </code></pre> <p>You can test it using the console:</p> <pre><code>> User.w_all_additional_metrics.first.computed_metric => "2" > User.w_all_additional_metrics.first.age_in_seconds => "633.136693954468" > User.w_all_additional_metrics.first.compound_computed_metric => "0.00315887551471441" </code></pre> <p>The second is ActiveRecord way, because your sql is not very complicate, so you can chain it in ActiveRecord Query, it's enough with some scopes :</p> <pre><code>class User < ActiveRecord::Base scope :w_computed_metric, proc { select('id*2 as computed_metric') } scope :w_age_in_seconds, proc { select('extract (epoch from (now()-created_at)) as age_in_seconds') } scope :w_compound_computed_metric, proc { select('computed_metric/age_in_seconds as compound_computed_metric') } def self.subquery select('*').w_computed_metric.w_age_in_seconds end def self.w_all_additional_metrics subquery.w_compound_computed_metric.from("(#{subquery.to_sql}) users") end end </code></pre> <p>This produces the following SQL:</p> <pre><code>SELECT *, id*2 as computed_metric, extract (epoch from (now()-created_at)) as age_in_seconds, computed_metric / age_in_seconds as compound_computed_metric FROM ( SELECT *, id*2 as computed_metric, extract (epoch from (now()-created_at)) as age_in_seconds FROM "users" ) users ORDER BY compound_computed_metric DESC LIMIT 1 </code></pre> <p>Hope it meets your requirement :)</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload