using .where with an array of parameters

Question

I am looking for a clean way to construct a query based upon an array of params it receives. To give some context a user can select filters to return the desired set of objects based upon the category name

class Category < ActiveRecord::Base
  has_many :bike_categories
  has_many :bikes, through: :bike_categories
end

class BikeCategory < ActiveRecord::Base
  # Holds bike_id and category_id to allow multiple categories to be saved per image, as opposed to storing an array of objects in one DB column
  belongs_to :bike
  belongs_to :category
end

class Bike < ActiveRecord::Base
  has_many :bike_categories, dependent: :destroy
  has_many :categories, through: :bike_categories
end

So in my case filtering categories for Bikes

(Mens, Womens, Mountain Bike, Hybrid).

It is the case that a Bike could have more than 1 category

So a user could select just mens, but also want to see Mens Mountain Bikes, in this case I would only want Bikes that have the categories Mens and Mountain Bikes

Currently i have

class Bike < ActiveRecordBase

  def self.search(params)
    bikes = joins(:categories)
    bikes = bikes.where(categories: { name: params[:name] }) if params[:name].present?
  end

end

I understand that I can chain queries so .where(param: value1).where(param: value2)

or that the array can be passed through

bikes.where(categories: { name: [params[:name]] }) if params[:name].present?

Which would perform an IN statement if i remember correctly.

How could I go about constructing this query so that I get the results I require

Thanks

Update

For example I have the following setup

Bike
 id: 1 title: 'Bike 1'

category
  id: 1 name: 'Mens'
  id: 2 name: 'Womens'
  id: 3 name: 'Mountain Bike'
  id: 4 name: 'Hybrid'

bike_categories
  id: 1 bike_id: 1 :category_id: 2
  id: 2 bike_id: 1 :category_id: 2

So I have a Bike that has two categories, Womens, Mountain Bike and I would like to just find that record

What’s wrong with `.where().where()`? It will return an intersection, exactly as you want. — Aleksei Matiushkin, Jul 26 '16 at 13:18
nothing wrong with it, just constructing multiple `.where()` if more than one param in array was a stumbling block — Richlewis, Jul 26 '16 at 13:21
I am still unsure, but won’t this work for you `params.each_with_object(bikes) { |param, query| query.where(...) }`? — Aleksei Matiushkin, Jul 26 '16 at 13:24
That doesnt seem to be doing what I want, i have updated the question though with more information if that helps, thanks — Richlewis, Jul 26 '16 at 14:00
Not trivial, take a look at http://stackoverflow.com/questions/4359455/sql-query-to-select-posts-belonging-to-multiple-categories — Mladen Jablanović, Jul 26 '16 at 14:37
thanks, any ideas how that translates to constructing the query ? — Richlewis, Jul 26 '16 at 14:39
take a look here: http://stackoverflow.com/questions/36131803/sql-where-joined-set-must-contain-all-values-but-may-contain-more but eventually you may just use raw SQL (let AR sanitize your params of course). — Mladen Jablanović, Jul 26 '16 at 14:41
thank you, ill give it a go (more complicated than I thought) — Richlewis, Jul 26 '16 at 14:43
yeah, this is one of the points where lots of developers just give up and fire N queries and let Ruby find the intersection at the end, making slow apps, later giving Rails bad name for poor performance. ;) — Mladen Jablanović, Jul 26 '16 at 14:46

using .where with an array of parameters

0 Answers0