Rails Active Record has many through find all partial matches-CodePudding

I am terrible at SQL but I need to find the best way to find a set of records that match a partial search/find that includes the ids of a has many through. I'm not sure of what the best way to do it is.

Ideal Query

Variant.where_option_ids(ids=[])
#returns ActiveRecord::Relation that contains all variants that have all option ids
 present (allowed to have extra option ids)

Models

class Option < ApplicationRecord
  has_many :option_variants
  has_many :variants, through: :options_variants
end

class Variant < ApplicationRecord
  has_many :options_variants
  has_many :options, through: :options_variants
end

class OptionsVariants < ApplicationRecord
  belongs_to :option
  belongs_to :variant
end

My first thought is a query like

OptionsVariant.where(option_id: ids).select(:variant_id).uniq

which would get me a list of variants but this would return partial matches. Where as I want the ids to be fully matched, as well as allowing it to have extra.

Should I be looking at JOINs?

Tests given a simple data set that would have 3 options applied to a variant with each combination being unique (results of Array#product)

[0,1,2].product([3,4,5], [6,7,8])
#= [[0, 3, 6], [0, 3, 7], [0, 3, 8], [0, 4, 6], [0, 4, 7], [0, 4, 8], [0, 5, 6], [0, 5, 7], [0, 5, 8], [1, 3, 6], [1, 3, 7], [1, 3, 8], [1, 4, 6], [1, 4, 7], [1, 4, 8], [1, 5, 6], [1, 5, 7], [1, 5, 8], [2, 3, 6], [2, 3, 7], [2, 3, 8], [2, 4, 6], [2, 4, 7], [2, 4, 8], [2, 5, 6], [2, 5, 7], [2, 5, 8]] 

expects(Variant.with_options([1]).count).to eq(9)

expects(Variant.with_options([1,4]).count).to eq(3)

expects(Variant.with_options([1,4,9]).count).to eq(1)

CodePudding user response：

Put this bit of tomfoolery in your Variant model:

def self.with_options(option_ids)
  self.joins(:options_variants)
      .where("options_variants.option_id IN (#{option_ids.map(&:to_i).join(', ')})")
      .group(:id)
      .having('COUNT(DISTINCT options_variants.option_id) >= ?', option_ids.length)
end

(And as a side-note, I would recommend renaming the associative table to be option_variants, so the model then is OptionVariant!)