Table-less select using Arel

Question

How would one write the following SQL query using only Arel functions?

select array_to_string(array(select name from tags, taggings where tags.id=taggings.id), ', ')

Caveat: This is an SQL fragment that is supposed to be part of a larger correlated sub-query - it may not make sense in isolation.

score 0 · Answer 1 · answered Jun 18 '13 at 04:56

Arel (atleast 3-0-stable) doesn't have the greatest support for named function, so it looks pretty ugly. The code below generates a version using a join

tags_tbl = Arel::Table.new("tags")
taggings_tbl = Arel::Table.new("taggings")
arel = Arel::Nodes::NamedFunction.new(:array_to_string, [
  Arel::Nodes::NamedFunction.new(:array, [
    Arel.sql(
      tags_tbl.project(
        tags_tbl[:name]
      ).join(
        taggings_tbl
      ).on(
        tags_tbl[:id].eq(taggings_tbl[:id])
      ).to_sql
    )
  ])
])
arel.to_sql # => array_to_string(array(SELECT "tags"."name" FROM "tags" INNER JOIN "taggings" ON "tags"."id" = "taggings"."id"))

Table-less select using Arel

1 Answers1