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
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)
like_all/like_any: queries = %w[ruby rails].map{|s|"%#{s}%"}; Product.where{title.like_all queries}.where methods (and all those others) could be chained! Makes perfect sense to use with the inject to create the query, very clever! I really appreciate the help!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
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.
