CODEWITHSUNDEEP
ruby-on-railsrubyruby-on-rails-3activerecordactivemodel
kashif
kashif

Reputation: 1147

How i can write a query inside select in rails

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

Upvotes: 4

Views: 17725

Answers (4)

Muntasim
Muntasim

Reputation: 6786

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

Upvotes: 0

Thaha kp
Thaha kp

Reputation: 3709

@referral = Referral.joins(:company).select([:id,:company_id]).where(id: 21).first

Then use

@referral.id
@referral.company_id
@referral.company.name

Upvotes: 2

Marek Lipka
Marek Lipka

Reputation: 51151

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

Upvotes: 3

Dan Grahn
Dan Grahn

Reputation: 9404

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

Upvotes: 3

Related Questions