I have a Postgres LIKE statement like this:
@favorites.find_each do |profiles|
@related_profiles = Profile.where('name LIKE ?', '%' + profiles.name + '%')
end
What I'm trying to do is loop through the favourites variable and find all profiles that contain some characters of the name.
For example a name "jasson jackson" should be found if the name contains "jackson" or "jasson"
The query you're looking for would be like:
Profile.where("name LIKE ?", "%#{profiles.name}%")
But note that your @related_profiles may not be properly assigned as the result would be the same as saying:
Profile.where("name LIKE ?", "%#{@favorites.last.name}%")
whereas, I doubt if that is what you need. Also note that the it would be an ActiveRecord::Collection, an array like object.
A way to work around that is to initialize @related_profiles = [] and then at each point through your loop, you could do:
@related_profiles +=
Profile.where("name LIKE ?", "%#{profiles.name}%")
or another way is:
names = @favorites.map(&:name)
query = names.map{|name| "name LIKE '%#{name}%'"}.join(" OR ")
OR
query = @favorites.map{|favorite| "name LIKE '%#{favorite.name}%'" }.join(" OR ")
THEN
profiles = Profile.where(query)
UPDATE Based on a comment from @joshrumbut, I decided to reimplement using the bind parameters. However, code clarity is a bit lost, but here's a way it could be done:
names = @favorites.map(&:name)
query = names.map{|favorite| "name LIKE ?" }.join(" OR ")
profiles = Profile.where(query, *names.map{|name| ("%#{name}%")})
Based on the comment from @muistooshort, I removed the quotes from the first two queries and I think this approach looks a bit cleaner, as he suggested. From the docs
Profile.where('name like any(array[?])', names.map { |s| "%#{s}%" })
where('name like any(array[?])', names.map { |s| "%#{s}%" }) is a cleaner option (docs). Also, the single quotes in first two aren't needed (and will cause problems) in the placeholder values, ActiveRecord will properly escape things for you.
For better results with this type of search, You can try use FTS (Full text search).
Rails has a gem with this feature implemented:
https://github.com/Casecommons/pg_search
PgSearch builds named scopes that take advantage of PostgreSQL's full text search.
With this gem installed on your project, just use this statement for search:
PgSearch.multisearch("jasson")
Other option is Elasticsearch, with him you can index yours registers for a better text search.
Hope it helps :)
I'll try with Postgres patterns and "OR" operator - postgres doc
names = @favorites.map(&:name)
Profile.where('name LIKE %(?)%', names.join('|'))
~ for that (and then you'd have to worry about escaping things that are special in regexes).