ruby on rails - ¿Cómo harías esta subconsulta de Rails usando Squeel?

ruby-on-rails activerecord

Question

Quiero reestructurar la consulta a continuación usando Squeel. Me gustaría hacer esto para poder encadenar a los operadores y volver a utilizar la lógica en las diferentes partes de la consulta.

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

La consulta debe funcionar en la base de datos y no debe ser una solución híbrida de Ruby, ya que tiene que ordenar y cortar millones de registros.

Configuré el problema para que funcione contra una tabla de user normal y para que pueda jugar con las alternativas.

Restricciones en una respuesta aceptable

• la consulta debe devolver un objeto User con todos los atributos normales
• cada objeto de usuario también debe incluir extra_metric_we_care_about , age_in_seconds y compound_computed_metric
• la consulta no debe duplicar ninguna lógica simplemente imprimiendo una cadena en varios lugares; quiero evitar hacer lo mismo dos veces
• [actualizado] Toda la consulta debe poder procesarse en la base de datos para que un conjunto de resultados que puede consistir en millones de registros se pueda ordenar y dividir en la base de datos antes de volver a Rails.
• [actualizado] La solución debería funcionar para una base de datos Postgres

Ejemplo del tipo de solución que me gustaría

La solución a continuación no funciona, pero muestra el tipo de elegancia que espero lograr

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

Debería poder ejecutar esto contra su base de datos existente

Tenga en cuenta que de alguna manera he ideado el problema para que pueda usar su clase de User existente y jugar con ella en su consola.

EDITAR

1. El DB que estoy usando es Postgres.
2. No estoy seguro de haber dejado 100% claro de que la consulta debería ejecutarse en DB. No puede ser una respuesta híbrida si parte de la lógica se hace esencialmente en Rails. Esto es importante ya que quiero poder ordenar y cortar millones de registros usando las columnas calculadas.

Answer 1

Es muy probable que esté totalmente equivocado. Siento que simplifica demasiado su problema para que otros lo entiendan. Como no puedo dar este código bien formateado en un comentario, ingreso la respuesta aquí.

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

Debajo de SQL es equivalente a su SQL anterior. Es por eso que digo que la subconsulta no es necesaria.

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

Si es correcto, entonces el compound_computed_metric se puede calcular de la siguiente manera. No se necesita una consulta personalizada.

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

Answer 2

Tengo 2 sulutions en tu caso. Mi base de datos es mysql, y simplifico tu código para demostración, creo que puedes extenderlo.

El primero es Squeel way, mezclé "sift" en Squeel y "from" en ActiveRecord Query. Instalé postgresql y probé mi solución en este momento, parece difícil usar "squeel" y "epoch from" juntos, pero encontré una forma alternativa en postgresql, llamada "date_part". También modifiqué el sql y reduje las duplicaciones de cálculo:

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

Produjo el 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

Puedes probarlo usando la consola:

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

El segundo es el modo ActiveRecord, porque su sql no es muy complicado, por lo que puede encadenarlo en ActiveRecord Query, es suficiente con algunos ámbitos:

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

Esto produce el siguiente 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

Espero que cumpla con su requisito :)

Answer 3

Vamos a comenzar con esto, ya que no es la respuesta que estás buscando ...

Ahora, con eso fuera del camino, esto es lo que probé y cómo se relaciona con los dos enlaces que publiqué en los comentarios de la pregunta.

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

Por lo tanto, ejecutar User.compound_metric en la consola arrojará los resultados que busca: un objeto User con los atributos adicionales: computed_metric , age_in_seconds y compound_metric . Desafortunadamente, esto viola la tercera restricción que usted colocó en una respuesta aceptable. Oh bien...

También probé algunas otras cosas (como puedes ver desde arriba):

El primer punto a destacar es la asociación autorreferencial, de la que estoy bastante orgulloso, aunque no nos lleve a donde queremos llegar.

belongs_to :myself, class_name: "User", foreign_key: :id

Esta ingeniosa pieza de código te permite acceder al mismo objeto a través de una unión. ¿Porque es esto importante? Bueno, Squeel solo le permitirá acceder a las asociaciones a través del método joins{} menos que le pase una cadena de SQL. Esto nos permite usar la función del filtro de Squeel; en este caso, no para filtrar los resultados, sino para incluir columnas agregadas del archivo db y dejar que Squeel haga un pesado aliasing y se una a las declaraciones. Puedes probarlo con el

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

La belleza de los tamices para lograr esto es la capacidad de sintonía y el azúcar sintético, es muy plano y legible.

El último bit que intenté jugar es .from{} . Antes de esta pregunta, ni siquiera sabía que existía. Estaba tan emocionado con la posibilidad de que me hubiera perdido algo tan simple como incluir una fuente para una consulta (en este caso, una sub selección). Prueba con 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

resultados en un 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''

(y sí, estoy probando contra una copia local de Arel y Squeel). No estoy lo suficientemente familiarizado con el funcionamiento interno de Arel para resolver el problema sin más esfuerzo (y muy probablemente un tenedor de Arel). Parece que Squeel simplemente pasa el método from{} método Arel from() sin hacer nada (más allá del resto de la magia que es Squeel).

Entonces, ¿dónde nos deja eso? Una solución que funciona, pero no es tan agradable y elegante como desearía que fuera, pero tal vez alguien más pueda aprovechar esto para una mejor solución.

PD: esto es con Rails v3.2.13 y la versión respectiva de Arel. La fuente de Rails v4 y Arel son bastante diferentes y no se han probado para esto.