Nested queries in Arel

Question

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

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

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?

Answer 1

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("*")

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

inner_query ->

select * from your_models where stuff='foo'

outer_query ->

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