ActiveRecord嵌套SELECT [英] ActiveRecord nested SELECT

问题描述

我需要嵌套嵌套SELECT的帮助。

I need help with SELECT FROM nested SELECT.

如何以ActiveRecord方式重写以下查询并获得一个Relation对象？

How can I rewrite following query in an ActiveRecord way and get an Relation object?

SELECT candidates.*
FROM (SELECT (candidates.first_name || ' ' || candidates.last_name) AS full_name, candidates.* FROM candidates) candidates
WHERE full_name = 'Anton Kolganov'

推荐答案

为什么要串联而不是根据姓和名进行选择？子选择的性能将比直接查询低得多。您可以使用 select 范围获得全名（它将以与其他属性相同的方式访问）：

Why do you concatenate instead of selecting based on first and last name? The subselect will be much less performant than direct query. You could get the full name using the select scope (it will be accessible in the same way as other attributes):

 Candidate.select("candidates.*, (first_name || ' ' || last_name) AS full_name").
           where(first_name: 'Antonov', last_name: 'Kolganov')

更新：如果您确实需要在ActiveRecord的 FROM 子句中使用子选择重写上述查询，则可以执行以下操作：

Update: If you really needed to rewrite the above query with a subselect in the FROM clause to ActiveRecord, you can do the following:

Candidate.from("(SELECT (candidates.first_name || ' ' || candidates.last_name) AS full_name, candidates.* FROM candidates) candidates").
          where(full_name: 'Anton Kolganov')

即，一般来说，您可以放ActiveRecord范围方法中的任何有效SQL，包括 select ， from 等。

I.e., in general, you can put any valid SQL into the ActiveRecord scope methods, including select, from, etc.

这篇关于ActiveRecord嵌套SELECT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！