CODEWITHSUNDEEP
postgresqlruby-on-rails-4activerecord
garo
garo

Reputation: 171

ActiveRecord Rails 4, how to look if array attribute contains any of values in array

Say I have a Book model that has a categories attribute which is an array. I want to create a scope in which I can grab all Books that contain at least one item from another array I want to pass in.

After googling extensively and trying out many different queries, the closest I've found is this:

Book.where("'Mystery' = ANY(categories)")

How can I substitute 'Mystery' to be something like ['Categ1', 'Categ2', etc...] so that I can retrieve any Book that contains AT LEAST ONE of the values in the array?

Upvotes: 3

Views: 1584

Answers (1)

mu is too short
mu is too short

Reputation: 434975

You need to know three things:

  1. PostgreSQL's array overlaps operator: &&. array1 && array2 is true if the two arrays have any elements in common.
  2. PostgreSQL's array constructor syntax, i.e. array[v1, v2, v3].
  3. If a placeholder's value is a Ruby array, then ActiveRecord will replace the ? placeholder with a comma-delimited list so where('c = any(array[?])', [1,2,3]) will look like c = any(array[1,2,3]) when it gets to the database.

If you put all those together, you get things like:

categories = %w[Categ1 Categ2 Categ3]
Book.where('categories && array[?]', categories)

and your scope would be like this:

scope :with_any_categories, ->(cats) { where('categories && array[?]', cats) }

or perhaps:

scope :with_any_categories, ->(*cats) { where('categories && array[?]', cats.flatten) }

depending on how you want to use the scope.

Upvotes: 5

Related Questions