ActiveRecord nested SELECT

Question

I need help with SELECT FROM nested SELECT.

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

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

Answer 1

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

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

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