How to use Arel::Nodes::TableAlias in an initial where statement

Question

I got stuck on this and for sure it's easy, but I just cannot find the solution in the docs.

I have some tree structure and the child where clause that I have to filter with an "exists" sub query:

current_node.children.as("children_nodes").where(Node.where(...).exists)

The Node.where.clause already joins to the children_nodes and it works if I use two different models. But how do I use the alias? Above code will result in:

NoMethodError (undefined method `where' for #<Arel::Nodes::TableAlias

It's so basic, but something I'm missing (I'm too new to arel).

Try with `current_node.children.as("children_nodes").Node.(where(...).exists)` didn't tested though. — Pavan, May 07 '14 at 08:30
current_node.children.as("children_nodes").Node will give me undefined method Node for # — Micha, May 07 '14 at 13:19

score 5 · Answer 1 · answered Jan 05 '18 at 17:55

5

You might be able to use the attribute table_alias which you can call on an Arel::Table.

Example:

# works
users = User.arel_table
some_other_table = Post.arel_table
users.table_alias = 'people'
users.join(some_other_table)

# doesn't work
users = User.arel_table.alias('people')
some_other_table = Post.arel_table
users.join(some_other_table)

answered Jan 05 '18 at 17:55

bigtoe416

53
1
5

2

Warning: the `arel_table` method always returns the same object (at least in Rails 4.2), so changing the `table_alias` has side effects. Using `User.arel_table.dup` works fine. – Michaël Witrant Apr 11 '19 at 16:23

score 0 · Answer 2 · edited Jun 20 '20 at 09:12

the as method generate an arel object which doesn't has where method such Relation object the Arel object generates a sql to be executed basically its a select manager you can use union and give it another condition then use to_sql for example:

arel_obj = current_node.children.as("children_nodes").Union(Node.where(....)

sql_string = arel_obj.to_sql

Node.find_by_sql(sql_string)

here is some links that might help http://www.rubydoc.info/github/rails/arel/Arel/SelectManager

score 0 · Answer 3 · answered Feb 17 '16 at 01:36

In Arel, as will take everything up to that point and use it to create a named subquery that you can put into a FROM clause. For example, current_node.children.as("children_nodes").to_sql will print something like this:

(SELECT nodes.* FROM nodes WHERE nodes.parent_id = 5) AS children_nodes

But it sounds like what you really want is to give a SQL alias to the nodes table. Technically you can do that with from:

current_node.children.from("nodes AS children_nodes").to_sql

But if you do that, lots of other things are going to break, because the rest of the query is still trying to SELECT nodes.* and filter WHERE nodes.parent_id = 5.

So I think a better option is to avoid using an alias, or write your query with find_by_sql:

Node.find_by_sql <<-EOQ
    SELECT n.*
    FROM   nodes n
    WHERE  n.parent_id = 5
    AND EXISTS (SELECT 1
                FROM   nodes n2
                WHERE  ....)
EOQ

Perhaps you could also make things work by aliasing the inner table instead:

current_node.children.where(
  Node.from("nodes n").where("...").select("1").exists
)

How to use Arel::Nodes::TableAlias in an initial where statement

3 Answers3