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 User object with all the normal attributes
  • each user object should also include extra_metric_we_care_about, age_in_seconds and compound_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
Edit
Report