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?

Squires

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.

Selfcontrol

this is the best way, because #from returns an ActiveRecord::Relation, which you can then join with other tables, etc. – Whitneywhitson

What about union on different tables? – Maceio

@Maceio As with any Union you would need to ensure the columns match both in count and in data type. Then the same code would function appropriately. That being said if you are querying a union across tables this answer would be bastardizing the concept of AR and the logic should likely be pulled out into a non-AR object. – Selfcontrol

Answer 2

You can do a union like this, concatenating the two sql queries.

sql1 = SipTrunk.where(default: true).limit(1).to_sql
sql2 = SipTrunk.where(default: false).limit(1).to_sql

@sip_trunks = SipTrunk.find_by_sql("(#{sql1}) UNION (#{sql2})")

---

If you want to be fancy or have more than one sql queries to join you can to this

final_sql = [sql1, sql2].join(' UNION ')
@sip_trunks = SipTrunk.find_by_sql(final_sql)

Anoa