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

The question is why would you need a "nested query"? We do not need to use "nested queries" this is thinking in the mindset of SQL not Relational Algebra. With relational algebra we derive relations and use the output of one relation as input to another so the following would hold true:

points = Table(:points, {:as => 'sorted'}) # rename in the options hash
final_points = points.order('timestamp DESC').group(:client_id, :timestamp).project(:client_id, :timestamp)

It's best if we leave the renaming to arel unless absolutely necessary.

Here the projection of client_id AND timestamp is VERY important since we cannot project all domains from the relation (i.e. sorted.*). You must specifically project all domains that will be used within the grouping operation for the relation. The reason being is there is no value for * that would be distinctly representative of a grouped client_id. For instance say you have the following table

client_id   |   score
----------------------
    4       |    27
    3       |    35
    2       |    22
    4       |    69

Here if you group you could not perform a projection on the score domain because the value could either be 27 or 69 but you could project a sum(score)

You may only project the domain attributes that have unique values to the group (which are usually aggregate functions like sum, max, min). With your query it would not matter if the points were sorted by timestamp because in the end they would be grouped by client_id. the timestamp order is irrelevant since there is no single timestamp that could represent a grouping.

Please let me know how I can help you with Arel. Also, I have been working on a learning series for people to use Arel at its core. The first of the series is at http://Innovative-Studios.com/#pilot I can tell you are starting to know how to since you used Table(:points) rather than the ActiveRecord model Point.