你将如何做到这一点的Rails使用Squeel子查询？ [英] How would you do this Rails sub-query using Squeel?

问题描述

我要重组下面使用Squeel查询。我想做到这一点，这样我可以链上的运营商，并重复使用的逻辑在查询中的不同部分。

  User.find_by_sql（选择
    用户。*，
    users.computed_metric，
    users.age_in_seconds，
    （users.computed_metric / age_in_seconds）作为compound_computed_metric
    从
    （
      选择
        用户。*，
        （users.id * 2）作为computed_metric，
        （提取物（从现在的时代（）） - 提取物（纪元从users.created_at））作为age_in_seconds
        从用户
    ）为用户）
 

的查询有向所有工作在DB和不应该是一个混合的Ruby溶液，因为其具有定购和切片的记录百万

我设置的问题，以便它应该运行较正常用户表，这样就可以用替代品进行播放。

在一个可以接受的答案限制

• 查询应返回用户对象，所有正常的属性
• 在每个用户对象还应包括 extra_metric_we_care_about ， age_in_seconds 和 compound_computed_metric
• 查询不应只是打印出一个字符串在多个地方复制任何逻辑 - 我想避免做同样的事情两次
• [更新]查询都应该做，能够在数据库中，这样的结果集，可能包括数以百万计的记录可以返回到Rails的
之前订购和切片在DB
• [更新]应该为一个Postgres数据库的解决方案

的解决方案类型的例子，我想

下面的解决方案不工作，但它显示了优雅是我希望实现的类型

 类用户的LT;的ActiveRecord :: Base的
＃这不工作 - 它只是说明了什么我想达到

  高清self.w_all_additional_metrics
    选择{ ['*'，
              computed_metric，
              age_in_seconds，
              （computed_metric / age_in_seconds）。如（compound_computed_metric）
      } {。从User.w.compound_computed_metric.w_age_in_seconds}
  结束

  高清self.w_computed_metric
    其中{'（ID * 2）作为computed_metric'}
  结束

  高清self.w_age_in_seconds
    其中{（摘录（时代从现在开始（）） - 提取物（纪元从created_at））作为age_in_seconds'}
  结束
结束
 

您应该能够对现有的数据库运行此

请注意，我已经有点做作的问题，以便您可以使用现有的用户类，并用它在你的控制台玩。的

修改

1. 在我使用的数据库是Postgres的。
2. 我不知道我做了它100％明确表示，查询应该在数据库中的所有执行。这不可能是一种混合的答案是一些逻辑的基本上在导轨进行。这很重要，因为我希望能够订购，切片数以百万计的使用计算列的记录。

解决方案

我有2 sulutions你的情况。我的数据库是MySQL和我简化您的code的演示，我觉得你可以扩展它。

首先是Squeel的方式，我在Squeel混合筛选和ActiveRecord的查询从。 我安装PostgreSQL和测试，我的解决方案就在刚才，这似乎很难用squeel和划时代的在一起，但我发现在PostgreSQL中的另一种方式，它被称为date_part数。我还修改了SQL和减少计算的重复：

 类用户的LT;的ActiveRecord :: Base的
  筛：w_computed_metric做
    （ID * 2）。如（computed_metric）
  结束

  筛：w_age_in_seconds做
    （date_part数（'划时代'，now.func） -  date_part数（'划时代'，created_at））。如（age_in_seconds）
  结束

  筛：w_compound_computed_metric做
    （computed_metric / age_in_seconds）。如（compound_computed_metric）
  结束

  高清self.subquery
    选择{['*'，过筛（w_computed_metric），过筛（w_age_in_seconds）]}
  结束

  高清self.w_all_additional_metrics
    选择{['*'，过筛（w_compound_computed_metric）]}从（（＃{subquery.to_sql}）的用户）。
  结束
结束
 

它产生的SQL：

  SELECT *，用户。computed_metric/用户。age_in_secondsAS compound_computed_metric
FROM（SELECT *，
             用户，ID* 2 AS computed_metric，
             date_part数（时代，现在（）） -  date_part数（时代，用户，created_at。）AS age_in_seconds从用户
     ）用户
 

您可以使用控制台对其进行测试：

 ＆GT; User.w_all_additional_metrics.first.computed_metric
=＆GT; 2
＆GT; User.w_all_additional_metrics.first.age_in_seconds
=＆GT; 633.136693954468
＆GT; User.w_all_additional_metrics.first.compound_computed_metric
=＆GT; 0.00315887551471441
 

二是ActiveRecord的方式，因为你的SQL是不是很复杂的，所以你可以连它的ActiveRecord查询，这是不够的一些范围：

 类用户的LT;的ActiveRecord :: Base的
  适用范围：w_computed_metric，PROC {选择（'ID * 2为computed_metric'）}
  适用范围：w_age_in_seconds，PROC {选择（提取物（从历元（NOW（） -  created_at））为age_in_seconds'）}
  适用范围：w_compound_computed_metric，PROC {选择（computed_metric / age_in_seconds为compound_computed_metric'）}

  高清self.subquery
    选择（*）。w_computed_metric.w_age_in_seconds
  结束

  高清self.w_all_additional_metrics
    subquery.w_compound_computed_metric.from（（＃{subquery.to_sql}）用户）
  结束
结束
 

这生成的SQL语句：

  SELECT
  *，ID * 2为computed_metric，
  提取物（从历元（NOW（） -  created_at））作为age_in_seconds，
  computed_metric / age_in_seconds为compound_computed_metric
从 （
    选择
      *
      ID * 2为computed_metric，
      提取物（从（时代今（） -  created_at））作为age_in_seconds
    从
      用户
    ）用户
ORDER BY compound_computed_metric降序
LIMIT 1
 

希望它符合您的要求：）

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_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

You should be able to run this against your existing database

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.

EDIT

1. The DB I'm using is Postgres.
2. I'm not sure I made it 100% clear that the query should all execute in the DB. It can't be a hybrid answer were some of the logic is essentially done in Rails. This is important since I want to be able to order and slice millions of records using the computed columns.

解决方案

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 :)

这篇关于你将如何做到这一点的Rails使用Squeel子查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！