如何在ActiveRecord中使用PostgreSQL的WITH语句？

Question

在PostgreSQL中，可以从“with”中读取数据。我想知道如何在Rails中使用它，而不必将整个查询放入原始SQL中。

这是一个示例查询：完全是为了这个问题而编造的。

with tasks as (select 1 as score, tasks.* from tasks)
select 1 from tasks where id > 10 order by tasks.score, tasks.id

在我的真实示例中，得分不仅仅是1，但对于这个示例，它可以正常工作。

下面是我设想的代码。

Task.with('tasks as (select 1 as score, tasks.* from tasks)')
    .where('id > 10')
    .order(score)
    .order(id)

我不太喜欢使用“with”，因为它是PG数据库特定的，但我真的需要按照计算出的值进行排序。我尝试创建一个视图，但在PG中创建视图需要精确指定字段，而我不希望其他编码人员在修改源表时还需要修改视图。

我真的希望能够链式操作。

- baash05

---

通用表达式（CTE，以 WITH 开头）不是 PostgreSQL 的专属功能，而是标准 SQL 功能。它也被 DB2、SQL Server 和 SQLite 支持，不确定其他品牌是否支持，但我相信它被广泛支持。 - Frank Heikens

Answer 1

我不相信这在纯粹的 ActiveRecord 中得到支持，除非退而使用原始的 SQL。

但是，有一个插件 postgres_ext，它可以添加 CTE 支持以在 ActiveRecord 中使用。我自己没有使用过这个插件（在这种情况下我更喜欢退回到原始的 SQL），但是看起来它可以实现您所寻找的链接行为。

安装完成后，您将需要使用它的 from_cte 方法来定义 CTE（即 WITH 语句），然后链接任何您想要的筛选、排序等操作。

因此，我认为代码应该像这样：

Task.from_cte('tasks', Task.where('id > 10')).order('tasks.score, tasks.id')

请注意，链接在from_cte调用完成后开始，在结果为ActiveRecord::Relation对象的情况下。 根据OP的评论进行编辑： 嗯，我认为你可以在from_cte中添加更多链接，这取决于你想要的CTE。您当然也可以通过where方法按用户过滤，使CTE仅包含单个用户的任务。

- khampson

---

额..那么它会查询所有记录的数据库，然后再进行限制..所以如果我的用户表有20万个用户，我想要其中一个用户的任务，我会通过关系获取所有任务吗？ - baash05