How can I match a partial string to a database's object's attribute? Regexp?
Solution 1
Use a query that searches a substring in between:
name = "Manhattan"
Movie.where("title like ?", "%#{name}%")
For example:
-
%Manhattan
will get you:Love in Manhattan
-
Manhattan%
will get:Manhattan and Company
-
%Manhattan%
will get you both:[Love in Manhattan, Manhattan and Company]
But, if you're searching through movies synopsis, you should use Thinking Sphinx or Elastic Search
For example, with Elastic Search, you could set the synopsis like this:
Add app/indices/movie_index.rb
:
ThinkingSphinx::Index.define :movie, :with => :active_record do
# fields
indexes title, :sortable => true
indexes synopsis
end
Index your data with rake ts:index
And then run Sphynx with: rake ts:start
You can search just like this:
Movie.search :conditions => {:synopsis => "Manhattan"}
Elastic Search is a great alternative to ThinkingSphinx, there's even a RailsCast about it, so you should definitely take a look to see what really suites you best... Hope this helps!
Solution 2
You do not need regex to find movies that have the search string. You can use SQL query like this:
Movie.where('title LIKE ?','Batman%')
That would return all movies start with "Batman"
Movie.where('title LIKE ?','%Batman%')
That would return all movies that have Batman anywhere in it's title. I think you figured out the '%' is a joker character in the query.
Dan Rubio
Updated on June 15, 2022Comments
-
Dan Rubio almost 2 years
I have a database containing a list of movies. A typical entry look like this:
id: 1, title: "Manhatten and the Murderer", year: 1928, synopsis: 'some text...' rating: 67, genre_id, etc. etc.
Now I'm trying to make a series of search tests pass and so far I have made a single test case pass where if you type the title "Manhatten and the Murderer" in a text field it will find the movie that you want. The problem is with partial matching.
Now I'd like a way to search "Manhat" and match the record "Manhatten and the Murderer". I also want it to match with any movie that has "Manhat" in it. For example, it would return maybe 2 or 3 others like title: "My life in Manhattan", title: "The Big Apple in Manhattan" etc. etc.
Below is the code that I have so far in my Movie model:
def self.search(query) # Replace this with the appropriate ActiveRecord calls... if query =~ where(title:) #where(title: query) binding.pry end end
My question is, how can I set this up? My problem is the "where(title:) line. One thought was to use Regexp to match the title attribute. Any help would be appreciated! Thanks.
-
Craig Ringer about 10 yearsand if you want case insensitive, in PostgreSQL you can use
ilike
. Or use the~*
operator and a regexp. -
Tony Zito over 9 yearsI see what you did there.