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;

Answer 2

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.

Answer 3

Point.
 from(Point.order(Point.arel_table[:timestamp].desc).as("sorted")).
 select("sorted.*").
 group("sorted.client_id")

Answer 4

Although I don't think this problem needs nested queries, like Snuggs mentioned. For those who do need nested queries. This is what I got working so far, not great but it works:

class App < ActiveRecord::Base   
  has_many :downloads

  def self.not_owned_by_users(user_ids)
    where(arel_table[:id].not_in( 
      Arel::SqlLiteral.new( Download.from_users(user_ids).select(:app_id).to_sql ) ) )
  end
end

class Download  < ActiveRecord::Base
  belongs_to :app
  belongs_to :user

  def self.from_users(user_ids)
    where( arel_table[:user_id].in user_ids )
  end

end

class User < ActiveRecord::Base
  has_many :downloads
end

App.not_owned_by_users([1,2,3]).to_sql #=>
# SELECT `apps`.* FROM `apps` 
# WHERE (`apps`.`id` NOT IN (
#   SELECT app_id FROM `downloads` WHERE (`downloads`.`user_id` IN (1, 2, 3))))
#

Answer 5

To do this in "pure" Arel, this worked for me:

points = Arel::Table.new('points')
sorted = Arel::Table.new('points', as: 'sorted')
query = sorted.from(points.order('timestamp desc').project('*')).project(sorted[Arel.star]).group(sorted[:client_id])
query.to_sql

Of course, in your case, points and sorted would be retrieved and tailored from the Points model as opposed to manufactured as above.