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

Question

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?

Vishal Jain · Answer 1 · 2022-03-08T05:56:14.120

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.

edited Mar 08 '22 at 05:56

answered Mar 07 '22 at 16:27

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

score 1 · Answer 2 · answered Mar 07 '22 at 16:42

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)
)

answered Mar 07 '22 at 16:42

David Aldridge

51,479
8
68
96

I gave it a try, David. It doesn't return any results. – yeniv Mar 07 '22 at 22:34

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

2 Answers2