9
I want to restructure the query below using Squeel. I'd like to do this so that I can chain the operators in it and re-use the logic in the different parts of the query.
User.find_by_sql("SELECT
users.*,
users.computed_metric,
users.age_in_seconds,
( users.computed_metric / age_in_seconds) as compound_computed_metric
from
(
select
users.*,
(users.id *2 ) as computed_metric,
(extract(epoch from now()) - extract(epoch from users.created_at) ) as age_in_seconds
from users
) as users")
The query has to all operate in the DB and should not be a hybrid Ruby solution since it has to order and slice millions of records.
I've set the problem up so that it should run against a normal user table and so that you can play with the alternatives to it.
Restrictions on an acceptable answer
- the query should return a
Userobject with all the normal attributes - each user object should also include
extra_metric_we_care_about,age_in_secondsandcompound_computed_metric - the query should not duplicate any logic by just printing out a string in multiple places - I want to avoid doing the same thing twice
- [updated] The query should all be do-able in the DB so that a result set that may consist of millions of records can be ordered and sliced in the DB before returning to Rails
- [updated] The solution should work for a Postgres DB
Example of the type of solution I'd like
The solution below doesn't work but it shows the type of elegance that I'm hoping to achieve
class User < ActiveRecord::Base
# this doesn't work - it just illustrates what I want to achieve
def self.w_all_additional_metrics
select{ ['*',
computed_metric,
age_in_seconds,
(computed_metric / age_in_s