Group entries based on associated model fields in aggregate
I´m always trying to avoid joins because they are creating duplicates that you´d have to remove with a group('id')
.
I´d rather use EXISTS
.
class Company < ApplicationRecord
has_and_belongs_to_many :keywords
scope :included, -> do
where(
Keyword
.included
.joins("INNER JOIN companies_keywords ON companies_keywords.keyword_id = keywords.id AND company_id = companies.id")
.arel.exists
).where.not(
Keyword
.excluded
.joins("INNER JOIN companies_keywords ON companies_keywords.keyword_id = keywords.id AND company_id = companies.id")
.arel.exists
)
end
scope :excluded, -> do
where(
Keyword
.excluded
.joins("INNER JOIN companies_keywords ON companies_keywords.keyword_id = keywords.id AND company_id = companies.id")
.arel.exists
)
end
scope :empty, -> do
where.not(
Keyword
.joins("INNER JOIN companies_keywords ON companies_keywords.keyword_id = keywords.id AND company_id = companies.id")
.where(included: [true, false])
.arel.exists
)
end
end
Also i recently discovered this Gem which simplifies exists queries. I find that particularly helpful. You could just write:
scope :included, -> { where_exists(:keywords, &:included).where_not_exists(:keywords, &:excluded) }
I didn´t test the performance on these but it should be pretty solid (as long as you added the right indexes).
Hope this answers your questions.