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.