Multiple CTEs with Arel

Question

I have an sql query of the following format:

 with from as (#{select * query}),
 to as (#{another select *}),
 rates as (#{yet another select *})
 select rates.* from rates, from, to
 where rates.from_id = from.id and rates.to_id = to.id

How can I convert this to Arel? I looked into Arel CTE, but there are no examples of using multiple aliases like in the query above.

Reindeer

You might consider using views if your use case supports it. There's even a library to treat a view like a model by Thoughtbot. – Widely

Answer 1

This should do the trick:

from_table = Arel::Table.new(:from)
to_table = Arel::Table.new(:to)
rates_table = Arel::Table.new(:rates)
query = rates_table.
  join(from_table).on(rates_table[:from_id].eq(from_table[:id])).
  join(to_table).on(rates_table[:to_id].eq(to_table[:id])).
  project(rates_table[Arel.star]).
  with([
    Arel::Nodes::As.new(from_table, Arel::Nodes::SqlLiteral.new("select * query")),
    Arel::Nodes::As.new(to_table, Arel::Nodes::SqlLiteral.new("another select *")),
    Arel::Nodes::As.new(rates_table, Arel::Nodes::SqlLiteral.new("yet another select *")),
  ])
puts query.to_sql

You should replace the Arel::Nodes::SqlLiteral.new("another select *") expressions with an actual Arel query. To get the Arel query from an ActiveRecord relation, you can call .ast on it. Example: User.where(active: true).ast.

Sunwise

I tried your answer, and only issue was reordering the last part. The working version looks like this gist.github.com/midhunkrishna/2adc07cd77002b5d49358ef69237b805 – Reindeer

@mátésolymosi, if you can assist with this, it would be appreciated. #55562092 – Godard