如何在Rails ActiveRecord中通用使用Common Table Expression(CTE)？

Common Table Expression（CTE，通用表表达式）是PostgreSQL、MySQL、Oracle、SQLite3等关系型数据库的常用特性，可以在多个查询部分复用同一个计算逻辑，还能实现递归查询这类复杂场景。

我之前找到过一款旧gem postgres_ext 支持这个功能，但它已经停止维护，而且只适配PostgreSQL。

目前网上有不少相关的旧问题，但它们要么针对特定Rails版本、特定数据库，要么依赖Arel：

• How do you use the postgresql WITH in activerecord?
• Rails 5.2.2 (active record) WITH statement
• Postgres Common Table Expression query with Ruby on Rails
• Arel and CTE to Wrap Query
• Multiple CTEs with Arel

请问有没有一种通用方法，能在Rails里通过ActiveRecord使用WITH子句？

通用解决方案 #

1. 直接拼接SQL字符串（最通用，适配所有支持CTE的数据库） #

如果不需要太复杂的ActiveRecord链式调用，直接手动拼好带WITH子句的SQL，用find_by_sql执行就行：

# 单CTE示例
cte_sql = <<~SQL
  WITH recent_orders AS (
    SELECT * FROM orders WHERE created_at >= NOW() - INTERVAL '7 days'
  )
  SELECT users.* FROM users
  JOIN recent_orders ON users.id = recent_orders.user_id
SQL

User.find_by_sql(cte_sql)

# 多CTE示例
cte_sql = <<~SQL
  WITH recent_orders AS (
    SELECT * FROM orders WHERE created_at >= NOW() - INTERVAL '7 days'
  ),
  high_value_users AS (
    SELECT user_id, SUM(amount) AS total_spent FROM recent_orders GROUP BY user_id HAVING SUM(amount) > 1000
  )
  SELECT users.* FROM users
  JOIN high_value_users ON users.id = high_value_users.user_id
SQL

User.find_by_sql(cte_sql)

这种方式完全贴合原生SQL语法，只要数据库支持CTE就能用，唯一缺点是没法用ActiveRecord的链式查询灵活调整条件。

2. 结合ActiveRecord的from方法（更贴合Rails风格） #

可以先把CTE的逻辑用ActiveRecord查询对象定义好，再通过from方法把CTE拼进去，配合链式调用：

# 定义CTE的子查询
recent_orders = Order.where('created_at >= ?', 7.days.ago).select('*')

# 构建带CTE的查询
users = User.from("WITH recent_orders AS (#{recent_orders.to_sql}) users")
            .joins('JOIN recent_orders ON users.id = recent_orders.user_id')
            .select('users.*')

users.to_a # 执行查询

多CTE的情况也可以拼接：

recent_orders = Order.where('created_at >= ?', 7.days.ago).select('*')
high_value_users = Order.from(recent_orders).select('user_id, SUM(amount) AS total_spent').group('user_id').having('SUM(amount) > 1000')

cte_clause = "WITH recent_orders AS (#{recent_orders.to_sql}), high_value_users AS (#{high_value_users.to_sql})"

users = User.from("#{cte_clause} users")
            .joins('JOIN high_value_users ON users.id = high_value_users.user_id')
            .select('users.*')

users.to_a

这种方式既能用ActiveRecord的查询构建能力，又能兼容不同数据库，只要数据库支持CTE就没问题。

3. 使用社区维护的通用gem #

如果不想手动拼SQL，可以用activerecord-cte这个gem，它支持Rails 5+，适配PostgreSQL、MySQL、SQLite等多种数据库，能让你用更优雅的方式定义CTE：

先在Gemfile里添加：

gem 'activerecord-cte'

然后用起来像这样：

# 单CTE
User.with(recent_orders: Order.where('created_at >= ?', 7.days.ago))
    .joins('JOIN recent_orders ON users.id = recent_orders.user_id')
    .select('users.*')

# 递归CTE（比如查询树形分类结构）
Category.with(recursive: true, tree_categories: <<~SQL)
  SELECT id, parent_id, name FROM categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.parent_id, c.name FROM categories c
  JOIN tree_categories tc ON tc.id = c.parent_id
SQL
.where('tree_categories.name LIKE ?', '%电子产品%')

这个gem封装了CTE的构建逻辑，不用自己拼SQL，还能保持ActiveRecord的链式调用体验。

内容的提问来源于stack exchange，提问作者mechnicov