Obtain Union all result in ActiveRecord

Question

Hows do one obtain the UNION operation result in Rails.

Given I have the following SQL statement

SELECT  "sip_trunks".* FROM "sip_trunks" WHERE "sip_trunks"."default" = t LIMIT 1 UNION ALL SELECT  "sip_trunks".* FROM "sip_trunks" WHERE "sip_trunks"."default" = f LIMIT 1 

Thus far I have managed to construct the SQL using AREL with union all statement.

SipTrunk.where(default: true).limit(1).union(:all,SipTrunk.where(default: false).limit(1))

But attempting to query this result and AREL i.e Arel::Nodes::UnionAll and I'm unable to obtain the DB result.

Also running to_sql on the statement yield a SQL like this..

( SELECT  "sip_trunks".* FROM "sip_trunks" WHERE "sip_trunks"."default" = $1 LIMIT 1 UNION ALL SELECT  "sip_trunks".* FROM "sip_trunks" WHERE "sip_trunks"."default" = $2 LIMIT 1 )

this seem like a prepared statement but I don't see any prepared statement in DB

Attempting to use the above SQL using find_by_sql

SipTrunk.find_by_sql(SipTrunk.where(default: true).limit(1).union(:all,SipTrunk.where(default: false).limit(1)).to_sql,[['default',true],['default',false]])

with following error

ActiveRecord::StatementInvalid: PG::SyntaxError: ERROR:  syntax error at or near "UNION"
LINE 1: ...trunks" WHERE "sip_trunks"."default" = $1 LIMIT 1 UNION ALL ...

How do I get the final SQL rows, from here?

Answer 1

Here is how I would perform this operation.

sql1 = SipTrunk.where(default: true).limit(1).arel
sql2 = SipTrunk.where(default: false).limit(1).arel
subquery = Arel::Nodes::As.new(
 Arel::Nodes::UnionAll.new(sql1,sql2),
 SipTrunk.arel_table
) 
SipTrunk.from(subquery)

This will result in the following SQL

SELECT 
  sip_trunks.*
FROM 
  ( SELECT 
      sip_trunks.* 
    FROM 
      sip_trunks
    WHERE 
      sip_trunks.default = t
    LIMIT 1
    UNION ALL 
    SELECT 
      sip_trunks.* 
    FROM 
      sip_trunks
    WHERE 
      sip_trunks.default = f
    LIMIT 1) AS sip_trunks

And this will return an ActiveRecord::Relation of SipTrunk objects.