如何在ActiveRecord中使用PostgreSQL的WITH查询？

当然可以在ActiveRecord里使用PostgreSQL的WITH（公共表表达式，CTE）查询，你的思路是对的，下面提供几种实用的实现方式：

1. 直接用原生SQL构建CTE #

你的示例代码可以正常运行，建议给聚合字段加上别名，方便后续获取结果：

User.with("user_sales as (select user_id, sum(amount) as total_sales from sales group by user_id)")
    .joins("LEFT JOIN user_sales on users.id = user_sales.user_id")
    .select("users.*, user_sales.total_sales")

这种方式适合快速实现复杂CTE逻辑，完全通过原生SQL定义公共表。

2. 结合ActiveRecord查询接口的优雅写法 #

如果想减少硬编码的原生SQL，可以用ActiveRecord查询对象定义CTE：

# 先构建CTE对应的查询逻辑
user_sales_cte = Sale.select(:user_id, "sum(amount) as total_sales").group(:user_id)

# 传入with方法自动生成CTE语句
User.with(user_sales: user_sales_cte)
    .joins("LEFT JOIN user_sales ON users.id = user_sales.user_id")
    .select("users.*, user_sales.total_sales")

这种写法更贴合Rails的查询风格，可读性和可维护性更强。

3. 递归CTE的实现 #

如果需要处理层级结构这类递归场景，同样可以通过with方法传入原生SQL实现：

User.with("recursive_user_hierarchy as (
            select id, parent_id, name from users where id = 1
            union all
            select u.id, u.parent_id, u.name from users u
            join recursive_user_hierarchy ruh on u.parent_id = ruh.id
          )")
    .from("recursive_user_hierarchy")
    .select("*")

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