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

Dissatisfaction

Try with current_node.children.as("children_nodes").Node.(where(...).exists) didn't tested though. – Jew

current_node.children.as("children_nodes").Node will give me undefined method Node for #<Arel::Nodes::TableAlias. Maybe you mixed something up? – Dissatisfaction

Answer 1

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)

Polymeric

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. – Tachograph

Answer 2

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

Ascent

Answer 3

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
)

Aversion

Answer 4

you can define an arel/table and use it in further calls (joins, wheres, orders, etc)

user = Arel::Table.new(User.table_name, as: 'u')
post = Arel::Table.new(Post.table_name, as: 'p')
puts user.join(post).on(user[:id].eq(post[:user_id]))
         .where(post[:rating].gt(4.5))
         .project(user[Arel.star]).to_sql

SELECT "u".* FROM "users" "u"
  INNER JOIN "posts" "p" ON "u"."id" = "p"."user_id"
WHERE "p"."rating" > 4.5

Petropavlovsk