ruby on rails - Consultas anidadas en Arel

ruby-on-rails

Question

Estoy intentando anidar consultas SELECT en Arel y / o Active Record en Rails 3 para generar la siguiente declaración SQL.

SELECT sorted.* FROM (SELECT * FROM points ORDER BY points.timestamp DESC) AS sorted GROUP BY sorted.client_id

Se puede crear un alias para la subconsulta haciendo

points = Table(:points)
sorted = points.order(''timestamp DESC'').alias

pero luego me quedo atascado como cómo pasarlo a la consulta principal (a falta de llamar a #to_sql , lo que suena bastante feo).

¿Cómo usa una instrucción SELECT como una sub-consulta en Arel (o Registro Activo) para lograr lo anterior? ¿Es posible que haya una forma completamente diferente de realizar esta consulta que no utilice consultas anidadas?

Answer 1

Aquí está mi acercamiento a las tablas temporales y Arel. Utiliza Arel # del método que pasa en la consulta interna con Arel # to_sql.

inner_query = YourModel.where(:stuff => "foo")
outer_query = YourModel.scoped  # cheating, need an ActiveRelation
outer_query = outer_query.from(Arel.sql("(#{inner_query.to_sql}) as results")).
                          select("*")

Ahora puede hacer algunas cosas bonitas con la consulta externa, paginar, seleccionar, agrupar, etc.

consulta_interior ->

select * from your_models where stuff=''foo''

extern_query ->

select * from (select * from your_models where stuff=''foo'') as results;

Answer 2

Aunque no creo que este problema necesite consultas anidadas, como mencionó Snuggs. Para aquellos que sí necesitan consultas anidadas. Esto es lo que conseguí trabajando hasta ahora, no excelente, pero funciona:

class App < ActiveRecord::Base   
  has_many :downloads

  def self.not_owned_by_users(user_ids)
    where(arel_table[:id].not_in( 
      Arel::SqlLiteral.new( Download.from_users(user_ids).select(:app_id).to_sql ) ) )
  end
end

class Download  < ActiveRecord::Base
  belongs_to :app
  belongs_to :user

  def self.from_users(user_ids)
    where( arel_table[:user_id].in user_ids )
  end

end

class User < ActiveRecord::Base
  has_many :downloads
end

App.not_owned_by_users([1,2,3]).to_sql #=>
# SELECT `apps`.* FROM `apps` 
# WHERE (`apps`.`id` NOT IN (
#   SELECT app_id FROM `downloads` WHERE (`downloads`.`user_id` IN (1, 2, 3))))
#

Answer 3

La pregunta es ¿por qué necesitaría una "consulta anidada"? No necesitamos usar "consultas anidadas", esto es pensar en la mentalidad de SQL, no en el álgebra relacional. Con el álgebra relacional derivamos relaciones y utilizamos la salida de una relación como entrada a otra, por lo que lo siguiente sería cierto:

points = Table(:points, {:as => ''sorted''}) # rename in the options hash
final_points = points.order(''timestamp DESC'').group(:client_id, :timestamp).project(:client_id, :timestamp)

Es mejor si dejamos el cambio de nombre a arel a menos que sea absolutamente necesario.

Aquí, la proyección de client_id AND timestamp es MUY importante ya que no podemos proyectar todos los dominios a partir de la relación (es decir, ordenados. *). Debe proyectar específicamente todos los dominios que se utilizarán dentro de la operación de agrupación para la relación. La razón es que no hay un valor para * que sea claramente representativo de un client_id agrupado. Por ejemplo, digamos que tiene la siguiente tabla

client_id   |   score
----------------------
    4       |    27
    3       |    35
    2       |    22
    4       |    69

Aquí, si agrupas, no puedes realizar una proyección en el dominio de puntuación porque el valor puede ser 27 o 69, pero puedes proyectar una suma (puntuación)

Solo puede proyectar los atributos de dominio que tienen valores únicos para el grupo (que normalmente son funciones agregadas como suma, máximo, mínimo). Con su consulta, no importaría si los puntos estuvieran ordenados por marca de tiempo porque al final se agruparían por client_id. el orden de la marca de tiempo es irrelevante, ya que no hay una sola marca de tiempo que pueda representar una agrupación.

Por favor, dime cómo puedo ayudarte con Arel. Además, he estado trabajando en una serie de aprendizaje para que las personas utilicen Arel en su esencia. El primero de la serie está en http://Innovative-Studios.com/#pilot. Puedo decirle que está comenzando a saber cómo usar la Tabla (: puntos) en lugar del Punto de modelo de ActiveRecord.

Answer 4

Para hacer esto en Arel "puro", esto funcionó para mí:

points = Arel::Table.new(''points'')
sorted = Arel::Table.new(''points'', as: ''sorted'')
query = sorted.from(points.order(''timestamp desc'').project(''*'')).project(sorted[Arel.star]).group(sorted[:client_id])
query.to_sql

Por supuesto, en su caso, los puntos y los ordenados se recuperarían y adaptarían del modelo de Puntos en lugar de fabricarlos como se indicó anteriormente.

Answer 5

Point.
 from(Point.order(Point.arel_table[:timestamp].desc).as("sorted")).
 select("sorted.*").
 group("sorted.client_id")