具有Arel的多个CTE [英] Multiple CTEs with Arel

问题描述

我有以下格式的sql查询：

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

如何将其转换为Arel？我调查了Arel CTE，但没有像上面的查询中那样使用多个别名的示例。

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.

推荐答案

这应该可以解决问题：

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

您应该用实际的Arel查询替换 Arel :: Nodes :: SqlLiteral.new（ another select *）表达式。要从ActiveRecord关系中获取Arel查询，可以在其上调用 .ast 。例如： User.where（active：true）.ast 。

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.

这篇关于具有Arel的多个CTE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！