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

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 only Jobs containing all of the Stacks?

CodePudding user response：

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

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

Another method could be to fetch all stacks name and use all? enumerator.

CodePudding user response：

You could try this:

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