postgresql Rails Activerecord 关系：使用子查询作为 SQL 选择语句的表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24345959/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Rails Activerecord Relation: using subquery as a table for a SQL select statement
提问by Vee
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:
有人可以帮我弄清楚如何使用 Rails（我使用 Rails 4）Activerecord 方法编写以下 SQL？我知道你可以用 find_by_sql 做到这一点，但我想保留活动记录关系。这是我正在尝试创建的 postGreSQL 数据库的 sql：
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):
对于我的子查询，我有以下内容（生成上面子查询的 sql）：
@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.
但是，我不知道如何编写一个使用@subquery 作为外部 select 语句表的 select 语句。
回答by leethax88
Use the from()method from the Active Record interface.
使用from()Active Record 界面中的方法。
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.
回答by fanjieqi
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')
