ruby-on-railsruby-on-rails-4activerecordunionarel

Obtain Union all result in ActiveRecord


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?


Solution

  • 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.