Arel 中的嵌套查询 [英] Nested queries in Arel

问题描述

我正在尝试在 Rails 3 中的 Arel 和/或 Active Record 中嵌套 SELECT 查询以生成以下 SQL 语句.

I am attempting to nest SELECT queries in Arel and/or Active Record in Rails 3 to generate the following SQL statement.

SELECT sorted.* FROM (SELECT * FROM points ORDER BY points.timestamp DESC) AS sorted GROUP BY sorted.client_id

可以通过执行创建子查询的别名

An alias for the subquery can be created by doing

points = Table(:points)
sorted = points.order('timestamp DESC').alias

但后来我被困在如何将它传递给父查询(没有调用 #to_sql，这听起来很丑陋).

but then I'm stuck as how to pass it into the parent query (short of calling #to_sql, which sounds pretty ugly).

如何在 Arel(或 Active Record)中使用 SELECT 语句作为子查询来完成上述操作?也许有一种完全不同的方式来完成这个不使用嵌套查询的查询?

How do you use a SELECT statement as a sub-query in Arel (or Active Record) to accomplish the above? Maybe there's an altogether different way to accomplish this query that doesn't use nested queries?

推荐答案

这是我处理临时表和 Arel 的方法.它使用 Arel#from 方法通过 Arel#to_sql 传入内部查询.

Here's my approach to temporary tables and Arel. It uses Arel#from method passing in the inner query with Arel#to_sql.

inner_query = YourModel.where(:stuff => "foo")
outer_query = YourModel.scoped  # cheating, need an ActiveRelation
outer_query = outer_query.from(Arel.sql("(#{inner_query.to_sql}) as results")).
                          select("*")

现在你可以用outer_query、paginate、select、group等做一些不错的事情了...

Now you can do some nice things with the outer_query, paginate, select, group, etc...

inner_query ->

inner_query ->

select * from your_models where stuff='foo'

outer_query ->

outer_query ->

select * from (select * from your_models where stuff='foo') as results;

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