CODEWITHSUNDEEP
ruby-on-railsactiverecordrails-activerecord
MGA
MGA

Reputation: 3751

Creating a where query in rails from an array

I need to make a where query from an array where each member of the array is a 'like' operation that is ANDed. Example:

SELECT ... WHERE property like '%something%' AND property like '%somethingelse%' AND ...

It's easy enough to do using the ActiveRecord where function but I'm unsure how to sanitize it first. I obviously can't just create a string and stuff it in the where function, but there doesn't seem to be a way possible using the ?.

Thanks

Upvotes: 1

Views: 4382

Answers (3)

mu is too short
mu is too short

Reputation: 434585

The easiest way to build your LIKE patterns is string interpolation:

where('property like ?', "%#{str}%")

and if you have all your strings in an array then you can use ActiveRecord's query chaining and inject to build your final query:

a = %w[your strings go here]
q = a.inject(YourModel) { |q, str| q.where('property like ?', "%#{str}%") }

Then you can q.all or q.limit(11) or whatever you need to do to get your final result.

Here's a quick tutorial on how this works; you should review the Active Record Query Interface Guide and the Enumerable documentation as well.

If you had two things (a and b) to match, you could do this:

q = Model.where('p like ?', "%#{a}%").where('p like ?', "%#{b}%")

The where method returns an object that supports all the usual query methods so you can chain calls as M.where(...).where(...)... as needed; the other query methods (such as order, limit, ...) return the same sort of object so you can chain those as well:

M.where(...).limit(11).where(...).order(...)

You have an array of things to LIKE against and you want to apply where to the model class, then apply where to what that returns, then again until you've used up your array. Thing that look like a feedback loop tend to call for inject (AKA reduce from "map-reduce" fame):

inject(initial) {| memo, obj | block } → obj

Combines all elements of enum by applying a binary operation, specified by a block or a symbol that names a method or operator.

If you specify a block, then for each element in enum the block is passed an accumulator value (memo) and the element [...] the result becomes the new value for memo. At the end of the iteration, the final value of memo is the return value for the method.

So inject takes the block's output (which is the return value of where in our case) and feeds that as an input to the next execution of the block. If you have an array and you inject on it:

a = [1, 2, 3]
r = a.inject(init) { |memo, n| memo.m(n) }

then that's the same as this:

r = init.m(1).m(2).m(3)

Or, in pseudocode:

r = init
for n in a
    r = r.m(n)

Upvotes: 6

aquajach
aquajach

Reputation: 2578

Let's say your array is model_array, try Array select:

model_array.select{|a|a.property=~/something/ and a.property=~/somethingelse/}

Of course you can use any regex as you like.

Upvotes: 0

Andrei S
Andrei S

Reputation: 6516

If you're using AR, do something like Model.where(property: your_array) , or Model.where("property in (?)", your_array) This way, everything is sanitized

Upvotes: 0

Related Questions