CODEWITHSUNDEEP
ruby-on-railsactiverecord
user3574603
user3574603

Reputation: 3618

Rails/ActiveRecord: How can I find rows in a table that point to rows in another that match all the specified column values?

I have models named Issue and Label. Each issue can have many labels. Each label can have many issues.

I am trying to build a query that will return an issue that contains all of the supplied labels.

For instance, if I supply ['bug', 'fix', 'enhancement'] I want issues that have all three of those three labels at least.

Currently, I have:

labels = ['bug', 'fix', 'enhancement']
Issue.joins(:labels).where(labels: { name: labels }).distinct

But this is insufficient as it returns issues that have at least one of the label names. I see this is because is because an 'IN' operator is generated:

WHERE "labels"."name" IN ('bug', 'fix', 'enhancement')

And from here on, I'm lost. The labels array can be any length. Can I get the result I want in a single query?

How can I find rows in a table that point to rows in another that match all the specified column values?

Upvotes: 0

Views: 464

Answers (1)

NeverBe
NeverBe

Reputation: 5038

I didn't check it, but maybe this approach could work

Issue.select('issues.id, count(labels.id) as cnt').includes(:labels).where(labels: { name: labels }).group('issues.id').having("cnt = #{labels.size}");

Upvotes: 1

Related Questions