1

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?

yeniv
  • 93
  • 8

2 Answers2

2

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.

Vishal Jain
  • 446
  • 3
  • 7
  • Hey Vishal, this doesn't return any results. – yeniv Mar 07 '22 at 22:33
  • 1
    Hi @yeniv, Sorry I didn't saw you were using postgresql. I have updated the query you can give it a try now. Also you can first try without adding the having clause to see what data is returned. – Vishal Jain Mar 08 '22 at 05:57
  • Nothing is returned, even without the having clause. – yeniv Mar 08 '22 at 23:32
  • 1
    @yeniv can you share the output of this `Job.joins(:stacks).select("STRING_AGG(stacks.name) AS stack_name").group('stacks.job_id').first.attributes` – Vishal Jain Mar 09 '22 at 05:46
  • Sure thing! The error reads: ActiveRecord::StatementInvalid: PG::UndefinedFunction: ERROR: function string_agg(character varying) does not exist LINE 1: SELECT STRING_AGG(stacks.name) AS stack_name FROM "jobs" INN... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. – yeniv Mar 09 '22 at 15:20
  • 1
    Seems like some value in `stacks.name` is other than string. `STRING_AGG` only works on string values. You can try this answer from here https://stackoverflow.com/a/33717650 – Vishal Jain Mar 09 '22 at 16:03
1

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
  • 51,479
  • 8
  • 68
  • 96