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.
User
object with all the normal attributesextra_metric_we_care_about
, age_in_seconds
and compound_computed_metric
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_seconds).as(compound_computed_metric)]
}.from{ User.w.compound_computed_metric.w_age_in_seconds }
end
def self.w_computed_metric
where{ '(id *2 ) as computed_metric' }
end
def self.w_age_in_seconds
where{ '(extract(epoch from now()) - extract(epoch from created_at) ) as age_in_seconds' }
end
end
Please note that I've somewhat contrived the problem so that you can use your existing User
class and play with it in your console.
I have 2 sulutions in your case. My database is mysql, and I simplify your code for demo, I think you can extend it.
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:
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
It produced the sql:
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
You can test it using the console:
> 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"
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 :
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
This produces the following SQL:
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
Hope it meets your requirement :)
It's very probably that I am totally wrong. I feel you simplify your problem too much in order to let others understand it. Since I can't give this well-formatted code in a comment, I input the answer here.
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
Below SQL is equivalent to your above SQL. That's why I say the sub-query is not necessary.
select
users.*,
(users.id *2 ) as computed_metric,
(extract(epoch from now()) - extract(epoch from users.created_at) ) as age_in_seconds,
computed_metric/age_in_seconds as compound_computed_metric
from users
If that's right, then the compound_computed_metric can be calculated in below way. No custom query is needed.
class User < ActiveRecord::Base
def compound_computed_metric
computed_metric/age_in_seconds
end
def computed_metric
self.id * 2
end
def age_in_seconds
Time.now - self.created_at
end
end
1.9.3p327 :001 > u = User.first
User Load (0.1ms) SELECT "users".* FROM "users" LIMIT 1
=> #<User id: 1, name: "spider", created_at: "2013-08-10 04:29:35", updated_at: "2013-08-10 04:29:35">
1.9.3p327 :002 > u.compound_computed_metric
=> 1.5815278998954843e-05
1.9.3p327 :003 > u.age_in_seconds
=> 126471.981447
1.9.3p327 :004 > u.computed_metric
=> 2
Let's preface this with it's not the answer you are looking for...
Now, with that out of the way, here is what I tried and how it relates to the two links I posted in the comments of the question.
class User < ActiveRecord::Base
# self-referential association - more on this later
belongs_to :myself, class_name: "User", foreign_key: :id
scope :w_computed_metric, ->() { select{[id, (id *2).as(computed_metric)]} }
scope :w_age_in_seconds, ->() { select{[id, (extract('epoch from now()') - extract('epoch from users.created_at')).as(age_in_seconds)]} }
scope :w_default_attributes, ->() { select{`*`} }
def self.compound_metric
scope = User.w_default_attributes.select{(b.age_in_seconds / a.computed_metric).as(compound_metric)}
scope = scope.joins{"inner join (" + User.w_computed_metric.to_sql + ") as a on a.id = users.id"}
scope = scope.joins{"inner join (" + User.w_age_in_seconds.to_sql + ") as b on b.id = users.id"}
end
sifter :sift_computed_metric do
(id * 2).as(computed_metric)
end
sifter :sift_age_in_seconds do
(extract(`epoch from now()`) - extract(`epoch from users.created_at`)).as(age_in_seconds)
end
def self.using_sifters_in_select
User.w_default_attributes.joins{myself}.select{[(myself.sift :sift_computed_metric), (myself.sift :sift_age_in_seconds)]}
end
def self.using_from
scope = User.w_default_attributes
scope = scope.select{[(age_in_seconds / computed_metric).as(compound_metric)]}
scope = scope.from{User.w_computed_metric.w_age_in_seconds}
end
end
So, running User.compound_metric
in console will yield the results you are looking for - a User
object with the additional attributes: computed_metric
, age_in_seconds
, and compound_metric
. Unfortunately, this violates the third constraint you placed on an acceptable answer. Oh well...
I also tried a few other things (as you can see from above):
First point of note is the self-referential association, which I'm quite proud of - even though it doesn't get us to where we want to go.
belongs_to :myself, class_name: "User", foreign_key: :id
This nifty piece of code lets you access the same object through a join. Why is this important? Well, Squeel will only allow you to access associations through the joins{}
method unless you pass it a string of SQL. This lets us use the sifter
s feature of Squeel - in this case not to filter the results, rather to include aggregate columns from the db and let Squeel do the heavy lifting of aliasing and joining the statements. You can test it with the
def self.using_sifters_in_select
User.w_default_attributes.joins{myself}.select{[(myself.sift :sift_computed_metric), (myself.sift :sift_age_in_seconds)]}
end
The beauty of sifters to acheive this is the chainability and syntatic sugar - it is very flat and readable.
The last bit I tried playing with is .from{}
. Before this question, I didn't even know it existed. I was soooo excited with the possibility that I had missed something so simple as including a source for a query (in this case a sub-select). Testing with using_from
def self.using_from
scope = User.w_default_attributes
scope = scope.select{[(age_in_seconds / computed_metric).as(compound_metric)]}
scope = scope.from{User.w_computed_metric.w_age_in_seconds}
end
results in a TypeError:
TypeError: Cannot visit Arel::SelectManager
from /home/prg10itd/projects/arel/lib/arel/visitors/visitor.rb:28:in `rescue in visit'
from /home/prg10itd/projects/arel/lib/arel/visitors/visitor.rb:19:in `visit'
from /home/prg10itd/projects/arel/lib/arel/visitors/to_sql.rb:348:in `visit_Arel_Nodes_JoinSource'
from /home/prg10itd/projects/arel/lib/arel/visitors/visitor.rb:21:in `visit'
from /home/prg10itd/projects/arel/lib/arel/visitors/to_sql.rb:139:in `visit_Arel_Nodes_SelectCore'
from /home/prg10itd/projects/arel/lib/arel/visitors/to_sql.rb:121:in `block in visit_Arel_Nodes_SelectStatement'
from /home/prg10itd/projects/arel/lib/arel/visitors/to_sql.rb:121:in `map'
from /home/prg10itd/projects/arel/lib/arel/visitors/to_sql.rb:121:in `visit_Arel_Nodes_SelectStatement'
from /home/prg10itd/projects/arel/lib/arel/visitors/visitor.rb:21:in `visit'
from /home/prg10itd/projects/arel/lib/arel/visitors/visitor.rb:5:in `accept'
from /home/prg10itd/projects/arel/lib/arel/visitors/to_sql.rb:19:in `accept'
(and yes, I'm testing against a local copy of the Arel and Squeel). I'm not familiar enough with the internal workings of Arel to resolve the issue without further effort (and most likely a fork of Arel). It does appear that Squeel just passes the from{}
method to the Arel from()
method without doing anything (beyond the rest of the magic that is Squeel).
So where does that leave us? A solution that works, but is not as nice and elegant as I wish it was - but maybe someone else can leverage this to a better solution.
PS - this is with Rails v3.2.13 and the respective version of Arel. The source for Rails v4 and Arel are quite different and not tested for this.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With