CODEWITHSUNDEEP
sqlruby-on-railsrubyactiverecord
fydelio
fydelio

Reputation: 953

ActiveRecord/SQL: Complex DB query (outer_join / union / ?)

DB query: I would like to get ONE database query with all participants of a training, showing all participants names and whether they already have this one specific skill. For building the result table (see sample) I would like to loop over the query result, without having to make a sub query for each employee.

|participant name|has_this_one_specific_skill|
|John Doe        |          yes              |
|Alex Hardacre   |          yes              |
|Abi Underwood   |          no               |
|Kevin Wallace   |          yes              |
|Connor Murray   |          no               |

Here is my DB structure. How would such a structure look like? Is this a left_outer_join or do I go with union?

enter image description here

Upvotes: 0

Views: 70

Answers (1)

Qaisar Nadeem
Qaisar Nadeem

Reputation: 2434

class Employee < ApplicationRecord
  has_many :participants
  has_many :trainings,through: :participants
  has_many :trained_skills,through: :trainings,source: :skill
end



class Training < ApplicationRecord
  belongs_to :skill
end


class Participant < ApplicationRecord
  belongs_to :training
  belongs_to :employee
end

Solution 1 - Recommended

   Employee.left_joins(:trained_skills).where({skills: {name: ["MYSQL",nil]}}).pluck "employees.name,CASE WHEN skills.id IS NOT NULL THEN 'yes' else 'no' end"

Note: You can use select instead of pluck if you need Employees Objects. ie ######.select "employees.name as participant_name,CASE WHEN skills.id THEN 'yes' else 'no' end as has_this_one_specific_skill"

Solution 2

 Employee.joins("LEFT OUTER JOIN participants
  ON participants.employee_id = employees.id LEFT OUTER JOIN trainings
  ON trainings.id = participants.training_id LEFT OUTER JOIN skills
  ON skills.id = trainings.skill_id AND skills.name = 'MYSQL'").
  pluck "employees.name,CASE WHEN skills.id IS NOT NULL THEN 'yes' else 'no' end"

Upvotes: 1

Related Questions