CODEWITHSUNDEEP
ruby-on-railsrubypostgresql
Ashley Banks
Ashley Banks

Reputation: 528

What type of query to use and how - JOIN / UNION

I'm pretty new to ruby and have tried hunting about for this but no solutions work to what I'm after.

I have an association of Companies to Jobs. So one Company has many jobs and each job belongs to a Company.

After searching for a Job - I want to query the associated Company ID that is saved to each Job.

So after searching for a Job title for example "Ruby" - I want to return all Jobs that match that title and their associated Company data. The below query kind of works the only problem is that the Jobs ID is replaced with the Company ID and I can never access each Jobs ID

@jobs_list = Jobs.select("jobs.*, companies.*").where("title LIKE ?", "%#{params[:jobs][:title]}%").joins("LEFT JOIN companies on jobs.company_id = companies.id")

Any suggestions / better practises?

Upvotes: 0

Views: 59

Answers (1)

PinnyM
PinnyM

Reputation: 35533

Firstly make sure your Job model has a line: belongs_to :company

Then use includes to eager load the company data:

@jobs = Jobs.where("title LIKE ?", "%#{params[:jobs][:title]}%").
             includes(:company)

You can now access the company data like this without any further database queries:

@jobs.each do |job|
  job_name = job.name
  company_name = job.company.name
end

Upvotes: 2

Related Questions