CODEWITHSUNDEEP
sqlruby-on-railsrubyruby-on-rails-4rails-activerecord
jeff
jeff

Reputation: 387

Pass parameter in the select of a query

I am building a query but I need to pass the parameters that I am sending to the model to be used in the query, the problem is that I can not make the query accept the parameters within the select, this is my query:

  def self.reporte_inventario_total(params)
    query = select("(SELECT Ruta FROM Rutas WHERE IdRutas = :rutaId or :rutaId = '') AS Ruta,
      ISNULL((SELECT S.Stock/PP.PzaXCja AS INT FROM stockhistorico S LEFT JOIN ProductosXPzas PP ON S.Articulo=PP.Producto WHERE S.Articulo=Productos.Clave AND S.RutaId = :rutaId or :rutaId = '' AND fecha = :fechaDiaO or :fechaDiaO = ''),0) AS  StockCajas,
      ISNULL((SELECT S.Stock%PP.PzaXCja AS INT FROM stockhistorico S LEFT JOIN ProductosXPzas PP ON S.Articulo=PP.Producto WHERE S.Articulo=Productos.Clave AND S.RutaId = :rutaId or :rutaId = '' AND fecha = :fechaDiaO or :fechaDiaO = ''),0) AS StockPiezas,
      Productos.Clave, Productos.Producto, Productos.CodBarras,Productos.Sector", ({rutaId: params[:search],fechaDiaO: params[:fechaDiaO]}))

  end

and this is the error that I get

unsupported: Hash

Upvotes: 3

Views: 3908

Answers (1)

Dmitry Sokurenko
Dmitry Sokurenko

Reputation: 6122

Your problem is that the select method unlike the where method expects a plain string as its argument, it doesn't allow ? and :variable placeholders.

To use expressions like :rutaId you need to wrap the select query with a call to sanitize_sql_array.

Assuming that reporte_inventario_total is a method on some ActiveRecord::Base subclass:

query_string = "SELECT Ruta FROM Rutas WHERE IdRutas = :rutaId or :rutaId ..."
query = select(sanitize_sql_array([query_string, rutaId: params[:search], fechaDiaO: params[:fechaDiaO]))

If reporte_inventario_total is a written outside of some ActiveRecord::Base subclass then you need to replace sanitize_sql_array(...) with something like ActiveRecord::Base.send(:sanitize_sql_array, ...), because that method is private and is intended to be used in models only.

Upvotes: 6

Related Questions