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.