How can I query my db for params by "and" instead of "or"

Question

I have habtm relation between products and colors. When I preform a query on products that are "red" and "black" I want it to return product that have "red" AND "black" associations not "red" OR "black"

This is my scope for this query:

scope :items_design_filter_color, -> (colors) { joins(:colors).where('colors.id' => colors.to_i) unless colors.nil? }

colors params

params[:colors] = ["1", "2"]

colors table is just id and name columns

Calling my scopes:

@products =

Kaminari.paginate_array(ItemsDesign.items_design_by_category(@category.subtree_ids)
                                        .items_design_filter_color(params[:colors])
                                        .items_design_filter_sort(sort)
                                        .items_design_filter_editors_pick(params[:editors_pick])
                                        .items_design_filter_sold_out(params[:sold_out])
                                        .items_design_filter_style(params[:style])
                                        .items_design_filter_store(params[:store])
                                        .items_design_filter_price(params[:low_end], params[:high_end]))
    .page(params[:page]).per(48)

this is my attempt at using one of the answers bellows method within my scope:

scope :items_design_by_category, -> (category) { joins(:items_categories).where('items_categories.id' => category) }
  scope :items_design_filter_color, -> (colors) { joins(:colors).where(colors: {id: colors}).each.select do |item|
                                                    (item.colors.map(&:id) & colors).size == colors.size
                                                  end unless colors.nil? }
  scope :items_design_filter_style, -> (styles) { where('items_style_id' => styles) unless styles.nil? }
  scope :items_design_filter_store, -> (stores) { where('store_id' => stores) unless stores.nil? }
  scope :items_design_filter_editors_pick, -> (editors_pick) { where('editors_pick' => TRUE) unless editors_pick.nil? }
  scope :items_design_filter_sold_out, -> (sold_out) { where('sold_out' => 'n') unless sold_out.nil? }
  scope :items_design_filter_price, -> (lowend, highend) { where('price_as_decimal' => lowend..highend) unless lowend.nil? }
  scope :items_design_filter_sort, -> (sort) { order(sort) unless sort.nil? }

Please show us how you do the or? By default (chaining where statements) only does AND. — nathanvda, Jun 22 '14 at 18:21
That scope is doing "or" by default. Currently when this query runs with multiple colors "red", "black" it will return products that are "red" and products that are "black" I want it to return products that have "red" and "black" associated with them. Leaving out products that have only "red" or only "black" associated with them. Hope that makes sense — bonum_cete, Jun 22 '14 at 18:35
Mmmmm weird, that would only work if `colors` (your parameter) is an array (which would translate to an `IN` sql-operator, and thus an implicit OR), but you do an explicit call `to_i` which would fail on an array. But I understand your problem now. Is the amount of colors fixed, e.g. always two? — nathanvda, Jun 22 '14 at 21:58

score 0 · Accepted Answer · answered Jun 23 '14 at 18:56

OK, well I ended up getting this way based on the someones answer that was deleted. If they want to come and put the answer back I will give them credit. In the meantime this solved my problem.

scope :items_design_filter_color, -> (colors) { color_ids = colors.map(&:to_i) unless colors.nil?
                                                includes(:colors).where(colors: {id: color_ids}).select do |item|
                                                  (item.colors.map(&:id) & color_ids).size == color_ids.size
                                                end unless colors.nil? }

How can I query my db for params by "and" instead of "or"

1 Answers1