CODEWITHSUNDEEP
ruby-on-railsregexdatabasepostgresql
Dan Rubio
Dan Rubio

Reputation: 4907

How can I match a partial string to a database's object's attribute? Regexp?

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.

Upvotes: 14

Views: 22149

Answers (3)

raviolicode
raviolicode

Reputation: 2175

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!

Upvotes: 43

Rails Fan
Rails Fan

Reputation: 1144

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.

Upvotes: 5

wurde
wurde

Reputation: 2627

One option is to run a search server alongside your Rails application. It is certainly my go to solution. This route offers a ton of features not found within Rails itself and might be overkill, but worth consideration.

I use Sphinx and implement it using the thinking-sphinx gem.

Resources:

http://pat.github.io/thinking-sphinx/

http://sphinxsearch.com/

Upvotes: 0

Related Questions