4

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
Charles Caldwell
  • 16,649
  • 4
  • 40
  • 47
kashif
  • 1,097
  • 4
  • 17
  • 32
  • Welcome to Stack Overflow! Please read the [newbie manual](http://grahn.us/projects/stack-overflow.php) for info on how to write better questions. For instance, you should explain what models you have and what you have already tried. – Dan Grahn Sep 09 '13 at 11:55
  • You can rewrite that query with a join instead of a subselect. – FlorinelChis Sep 09 '13 at 12:59

4 Answers4

3

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

Dan Grahn
  • 9,044
  • 4
  • 37
  • 74
2
@referrals = Referral.select('id, company_id, (SELECT name FROM companies WHERE  id = referred_to_id) AS name').where(company_id: 21)
Marek Lipka
  • 50,622
  • 7
  • 87
  • 91
2
@referral = Referral.joins(:company).select([:id,:company_id]).where(id: 21).first

Then use

@referral.id
@referral.company_id
@referral.company.name
Thaha kp
  • 3,689
  • 1
  • 26
  • 25
0

Using activerecord you can achieve that by:

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

But if you really want face those attributes only you can use:

record = Referral.where("referrals.company_id = 21").joins("left join companies on referrals.referred_to_id = companies.id").select("referrals.id , referrals.company_id, companies.name as name").first

Now you can access that special object's attributes as:

record.id
record.company_id
record.name
Muntasim
  • 6,689
  • 3
  • 46
  • 69