0

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.

MIdhun Krishna
  • 1,739
  • 1
  • 13
  • 31
  • 1
    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](https://github.com/thoughtbot/scenic). – coreyward Jul 14 '17 at 00:30

1 Answers1

3

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.

Mate Solymosi
  • 5,699
  • 23
  • 30
  • I tried your answer, and only issue was reordering the last part. The working version looks like this https://gist.github.com/midhunkrishna/2adc07cd77002b5d49358ef69237b805 – MIdhun Krishna Jul 14 '17 at 06:20
  • @mátésolymosi, if you can assist with this, it would be appreciated. https://stackoverflow.com/questions/55561592/rails-5-2-2-active-record-with-statement – user2012677 Apr 08 '19 at 12:12