CODEWITHSUNDEEP
sqlruby-on-rails
ckeat9
ckeat9

Reputation: 162

rails query record that must meet all condition

i have the following relationship:

class Variant < ActiveRecord::Base
     has_many :option_value_variants
     has_many :option_values, through: :option_value_variants
end

class OptionValueVariant < ActiveRecord::Base
    belongs_to :option_value
    belongs_to :variant
end

class OptionValue < ActiveRecord::Base
    has_many :option_value_variants
    has_many :variants, through: :option_value_variants
end

here is my database for option value variant:

id  | variant_id | option_value_id
24  |     21     |        72
22  |     20     |        72
26  |     22     |        71
20  |     19     |        71
25  |     22     |        70
21  |     20     |        70

i was trying to get the variant_id after inputting option_value_id as my parameters. the condition for the variant_id is, it must have all the option_value_id.

below is the quote of my controller:

variant = OptionValueVariant.where(option_value_id: params[:variant][:option_value_ids]).uniq.pluck(:variant_id)

example if my parameters for option_value_id is '72' & '70', i hope to get my result '[20]' because this variant_id has both option_value_id of '72' & '70.

instead i get an array of '[20,21,22]', which is not what i wanted because some variant_id only have one of the option_value_id.

i had been searching for days with no answer, probably the real question im supposed to ask.

please assist and much appreciated on all helps,

thanks.

Upvotes: 1

Views: 585

Answers (2)

dp7
dp7

Reputation: 6749

Just a way around solution:

Note: This solution assumes you have no row for two same foreign keys combination like in the following example.

id  | variant_id | option_value_id
22  |     20     |        72
22  |     20     |        72


variant = OptionValueVariant.where(option_value_id: params[:variant][:option_value_ids]).pluck(:variant_id)
variant.select {|i| variant.count(i) == params[:variant][: option_value_ids].uniq.count  }.uniq

Upvotes: 1

Sixty4Bit
Sixty4Bit

Reputation: 13402

You are trying to find the intersection of the Variant id's. You need a set of id's for each OptionValue you are checking. In your case, you should do this:

variant_ids = []
params[:variant][:option_value_ids].each do |ovi|
  variant_ids << OptionValueVariant.where(option_value_id: ovi).ids
end

uniq_ids = variant_ids.inject(&:&)
  1. variant_ids is an array of matching variant_ids.
  2. Then you add an array of the ids that match for each option_value_id
  3. Then you use intersection (array & array) to find the ids that are in all of the arrays. The inject method loops over all of the arrays and calls the #& method on each item in the array. In this case it happens to be another array and you are calling the intersection method on each one.

This will work for when you have at least one option_value_ids.

Upvotes: 0

Related Questions