How i can write a query inside select in rails

Question

How can I write this query in Ruby on Rails? Query inside a select

SELECT id, 
       company_id, 
       (SELECT name 
        FROM   companies 
        WHERE  id = referred_to_id) AS name 
FROM   referrals 
WHERE  company_id = 21 

Answer 1

@referrals = Referral.select('id, company_id, (SELECT name FROM companies WHERE  id = referred_to_id) AS name').where(company_id: 21)

Answer 2

In Rails you don't really need to worry about writing SQL like this. ActiveRecord handles the creation of all your simple SQL commands.

The code below will give you the company name so long as you have set up your relationships correctly in the models.

@referral = Referral.find(21)
@referral.company.name

See this tutorial on Active Record Associations