How do I write this Rails ActiveRecord many-to-many query?

user_7381935 0
Viewed 48

I have a many-to-many relationship between Jobs & Stacks via JobStacks. I'm trying to write an ActiveRecord query to return Jobs containing ALL of the Stacks passed to it.

class Job < ApplicationRecord  
  has_many :job_stacks
  has_many :stacks, through: :job_stacks
end

class JobStack < ApplicationRecord
  belongs_to :job
  belongs_to :stack
end

class Stack < ApplicationRecord
  has_many :job_stacks, dependent: :destroy
  has_many :jobs, through: :job_stacks
end

This creates an SQL IN query returning Jobs containing ANY of the stacks.

Job.joins(:stacks).where(stacks: {name: ['JavaScript', 'Python']})

Is there a similar way to write this returning Jobs containing all of the Stacks as a .where call from Jobs?

postgresql activerecord ruby-on-rails
2 Answers

This is a one method you can use to fetch result with just using DB STRING_AGG function

Job.joins(:stacks).select("STRING_AGG(stacks.name) AS stack_name").group('stacks.job_id').having("stack_name= 'JavaScript,Python'")

There is another method ARRAY_AGG, I haven't used that but I think you can try with this as well.

user_130835460

You could try this:

names = ['JavaScript', 'Python']
Job.where(id: 
  Stack.
    where(name: names).
    select(:job_id).
    group(:job_id).
    having("count(*) >= ?", names.size)
)
David_Aldridge_67420
Related