use union all in rails

Question

Is it possible to use union all in rails? I'm putting together a query but I need to use union all, is there any way to adapt it to rails? Here's my query:

select sum(DV.Importe + DV.iva) 
from venta INNER JOIN DetalleVet DV ON DV.RutaId = [venta].RutaID AND DV.IdEmpresa = [venta].IdEmpresa AND DV.Docto = [venta].Documento 
where venta.Rutaid=8 and diao=611 and venta.TipoVta='Contado'
union all 
select SUM(DP.Importe + DP.Iva) from pedidosliberados PL inner join detallepedidoliberado DP on PL.PEDIDO=DP.PEDIDO and PL.RUTA=DP.RUTA and  PL.IDEMPRESA=DP.idempresa
where  PL.cancelada=0 and  PL.status=5 and PL.Tipo='Contado' and PL.Ruta=8 and PL.DiaO_entrega=611

Given the complexity of that statement what do you expect to return because AR objects seem pointless and the unaliased `sum()` seems like an `Array` would be sufficient? If so then using `Arel` to build the query would be best IMO — engineersmnky, Jun 26 '18 at 20:40
you can use the [active_record_union](https://github.com/brianhempel/active_record_union) gem — max pleaner, Jun 26 '18 at 21:04

Joseph Cho · Answer 1 · 2018-06-26T21:33:10.823

Probably the simplest way to adapt your query to rails is to use the find_by_sql method. It looks like this:

Model.find_by_sql

So translating your query it could look something like this.

Client.find_by_sql("
  SELECT Sum(DV.importe + DV.iva) 
  FROM   venta 
         INNER JOIN detallevet DV 
                 ON DV.rutaid = [venta].rutaid 
                    AND DV.idempresa = [venta].idempresa 
                    AND DV.docto = [venta].documento 
  WHERE  venta.rutaid = 8 
         AND diao = 611 
         AND venta.tipovta = 'Contado' 
  UNION ALL 
  SELECT Sum(DP.importe + DP.iva) 
  FROM   pedidosliberados PL 
         INNER JOIN detallepedidoliberado DP 
                 ON PL.pedido = DP.pedido 
                    AND PL.ruta = DP.ruta 
                    AND PL.idempresa = DP.idempresa 
  WHERE  PL.cancelada = 0 
         AND PL.status = 5 
         AND PL.tipo = 'Contado' 
         AND PL.ruta = 8 
         AND PL.diao_entrega = 611
")
# => [<Client id: 1, sum: 22 >, <Client id: 2, sum: 44>...]

Alternatively if you don't have a model and just want to execute raw SQL, ActiveRecord has a neat helper function called execute.

sql = "Select * from ... your sql query here"
records_array = ActiveRecord::Base.connection.execute(sql)

Reference here.

use union all in rails

1 Answers1