16

Can somebody help me figure out how to write the following SQL using Rails (I'm using Rails 4) Activerecord methods? I know you can do this with find_by_sql but I'd like to preserve the active record relation. Here's the sql for a postGreSQL db that I'm trying to create:

SELECT * FROM 
(SELECT DISTINCT ON(table_a.id) table_a.name as alias_a, table_b.id, table_b.time
FROM table_1
LEFT OUTER JOIN table_b ON table_a.id = table_b.id
ORDER BY table_a.id, table_b.time asc) AS subquery
ORDER BY alias_a asc

For my subquery, I have the following (which generates the sql of the subquery above):

@subquery = table_a.select("DISTINCT ON(table_a.id) table_a.name as alias_a, table_b.time")     
@subquery = @subquery.joins("LEFT OUTER JOIN table_b ON table_a.id = table_b.id")
@subquery = @subquery.order("table_a.id, table_b.time asc")

But, I can't figure out how to write a select statement that uses @subquery as the table for the outer select statement.

zishe
  • 10,665
  • 12
  • 64
  • 103
Vee
  • 1,821
  • 3
  • 36
  • 60

2 Answers2

21

Use the from() method from the Active Record interface.

For example:

@subquery = table_a.select("DISTINCT ON(table_a.id) table_a.name as alias_a, table_b.time")     
@subquery = @subquery.joins("LEFT OUTER JOIN table_b ON table_a.id = table_b.id")
@subquery = @subquery.order("table_a.id, table_b.time asc")

Then use it like this in the outer query:

@query = OtherModel.from("(#{@subquery.to_sql}) table_name, other_model_table, etc ...").where(:field => table_name.alias_a) ...etc.
Sébastien
  • 11,860
  • 11
  • 58
  • 78
leethax88
  • 340
  • 3
  • 5
  • Thanks! For future reference, I found a list of query methods for rails at: http://api.rubyonrails.org/classes/ActiveRecord/QueryMethods.html#method-i-from – Vee Oct 01 '14 at 18:16
4

This one is more elegant:

@subquery = Model.balalalala
@query = OtherModel.from(@subquery, :sub_query).where(sub_query: {column_b: balalala}).order('sub_query.column_a')
fanjieqi
  • 472
  • 4
  • 11