Arel::Nodes::Union does not have an alias #as

[itsNikolay]

Arel::Nodes::Union does not have alias #as:

> Arel::Table.new('users')
     .project(?*)
     .from(
        Arel::Table.new('users').project(?*)
       .union(Arel::Table.new('users').project(?*))
       .as('cant_get_alias')
      ).to_sql

> NoMethodError: undefined method `as' for #<Arel::Nodes::Union:0x007fe9b49dc948>

But Arel::Table has it:

> Arel::Table.new('users')
   .project(?*)
   .from(
       Arel::Table.new('users').project(?*)
       .as('can_get_alias')
    ).to_sql
"SELECT * FROM (SELECT * FROM \"users\") can_get_alias"

Without an alias the PostgreSQL returns exception:

PG::SyntaxError: ERROR:  subquery in FROM must have an alias

Arel version: 6.0
PostgreSQL version: 9.4.5

[lserman]

@itsNikolay I just ran into this as well, did you find any workaround without resorting to raw SQL?

[itsNikolay]

@lserman only with a raw sql

[burnt43]

The best solution I could come up with is you can take the Arel::Nodes::Union object and feed it as an argument to Arel.sql by calling to_sql on it. The result is a Arel::Nodes::SqlLiteral object which does have the as method. Then you can create a third Arel::Table with the same name as what you plan to alias the union as.

a = Arel::Table.new('aaas') # 1st table I want to UNION
b = Arel::Table.new('bbbs') # 2nd table I want to UNION
c = Arel::Table.new('cccs') # a table that represents the UNION of a and b

He is a stupid example:
Suppose table a and b both have a name column. I union the names from table a and b and alias the result using the same name as table c. Then I do a select on table c for fun.

c.from( Arel.sql( a.where(a[:id].eq(1)).project(a[:name]).union(b.where(b[:id].eq(2)).project(b[:name])).to_sql ).as(c.table_name) ).where(c[:name].eq('James')).project(c[:name]).to_sql

The result of that code is:

=> "SELECT `cccs`.`name` FROM ( SELECT `aaas`.`name` FROM `aaas` WHERE `aaas`.`id` = 1 UNION SELECT `bbbs`.`name` FROM `bbbs` WHERE `bbbs`.`id` = 2 ) AS cccs WHERE `cccs`.`name` = 'James'"

[burnt43]

After about on hour of working on more complicated real world examples my solution is no good. I just decided to monkey patch the method in.

module ::Arel
  module Nodes
    class Union
      include Arel::AliasPredication
    end
  end
end

This will give you the as method.

[palkan]

@itsNikolay Try this:

Arel::Table.new('users')
     .project(?*)
     .from(
        Arel::Table.new('users').project(?*)
       .union(Arel::Table.new('users', as: 'aliased_users').project(?*))
      ).to_sql

[burnt43]

I somehow ran into this problem again. Just monkey patching the Union class is not good enough. Calling as on a Union object gives you back a Arel::Nodes::As object. If you feed that object into some activerecord methods like joins it won't work because Arel::Nodes::As does not have some methods it will eventually call like table_name.

I found the best way to do this is to just manually create a Arel::Nodes::TableAlias alias object. This is what Arel::Table.new('foobar').project("*").as('hoge') will give you, but with my monkey patch you will get an Arel::Nodes::As by doing Arel::Table.new('foobar').project("*").union(Arel::Table.new('thing').project("*")).as('hoge')

So instead just do Arel::Nodes::TableAlias.new(Arel::Table.new('foobar').project("*").union(Arel::Table.new('thing').project("*")).as('hoge'), 'alias_name')

This seems to work OK for me now.

[hqt]

@burnt43 thanks. it works for me. In my query, I have added custom column. How can I get data after union ? (because no model fits that)

[matthewd]

Per #523, Arel development is moving to rails/rails.

If this issue is still relevant, please consider reopening it over there. (Note that the Rails repository does not accept feature request issues, and requires reproduction steps that rely on Active Record's documented API.)