CODEWITHSUNDEEP
ruby-on-railsrubyareltable-alias
Micha
Micha

Reputation: 302

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

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

Upvotes: 12

Views: 4777

Answers (4)

blb
blb

Reputation: 41

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

Upvotes: 0

bigtoe416
bigtoe416

Reputation: 53

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)

Upvotes: 5

Paul A Jungwirth
Paul A Jungwirth

Reputation: 24541

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
)

Upvotes: 0

H.Elsayed
H.Elsayed

Reputation: 431

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

Upvotes: 0

Related Questions