Rails Activerecord 关系:使用子查询作为 SQL 选择语句的表 [英] Rails Activerecord Relation: using subquery as a table for a SQL select statement

问题描述

谁能帮我弄清楚如何使用 Rails(我使用 Rails 4)Activerecord 方法编写以下 SQL?我知道你可以用 find_by_sql 做到这一点，但我想保留活动记录关系.这是我正在尝试创建的 postGreSQL 数据库的 sql:

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

对于我的子查询，我有以下内容(生成上面子查询的 sql):

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")

但是，我不知道如何编写使用@subquery 作为外部 select 语句表的 select 语句.

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

推荐答案

使用 Active Record 接口中的 from() 方法.

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

例如:

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

这篇关于Rails Activerecord 关系:使用子查询作为 SQL 选择语句的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！