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.
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.