postgresql 如何将部分字符串与数据库对象的属性相匹配？正则表达式？

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

提问by Dan Rubio

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.

现在我想要一种搜索​​“Manhat”并匹配记录“Manhatten and the Murderer”的方法。我还希望它与任何包含“Manhat”的电影相匹配。例如，它可能会返回其他 2 或 3 个类似标题：“我在曼哈顿的生活”，标题：“曼哈顿的大苹果”等。

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.

我的问题是，我该如何设置？我的问题是“where(title:) 行。一个想法是使用正则表达式来匹配标题属性。任何帮助将不胜感激！谢谢。

回答by raviolicode

Use a query that searches a substring in between:

使用在两者之间搜索子字符串的查询：

name = "Manhattan"
Movie.where("title like ?", "%#{name}%")

For example:

例如：

• %Manhattanwill get you: Love in Manhattan
• Manhattan%will get: Manhattan and Company
• %Manhattan%will get you both: [Love in Manhattan, Manhattan and Company]

• %Manhattan会让你： Love in Manhattan
• Manhattan%将得到： Manhattan and Company
• %Manhattan%会让你俩： [Love in Manhattan, Manhattan and Company]

But, if you're searching through movies synopsis, you should use Thinking Sphinxor Elastic Search

但是，如果您要搜索电影概要，则应该使用Thinking Sphinx或Elastic Search

For example, with Elastic Search, you could set the synopsis like this:

例如，使用 Elastic Search，您可以像这样设置概要：

Add app/indices/movie_index.rb:

添加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

索引您的数据 rake ts:index

And then run Sphynx with: rake ts:start

然后使用以下命令运行 Sphynx： rake ts:start

You can search just like this:

你可以这样搜索：

Movie.search :conditions => {:synopsis => "Manhattan"}

Movie.search :conditions => {:synopsis => "Manhattan"}

---

---

Elastic Search is a great alternative to ThinkingSphinx, there's even a RailsCastabout it, so you should definitely take a look to see what really suites you best... Hope this helps!

Elastic Search 是 ThinkingSphinx 的绝佳替代品，甚至还有关于它的RailsCast，所以你一定要看看什么最适合你......希望这会有所帮助！

回答by Rails Fan

You do not need regex to find movies that have the search string. You can use SQL query like this:

您不需要正则表达式来查找具有搜索字符串的电影。您可以像这样使用 SQL 查询：

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.

这将返回所有标题中包含蝙蝠侠的电影。我想你发现 '%' 是查询中的一个小丑字符。

回答by Andy Bettisworth

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.

一种选择是在 Rails 应用程序旁边运行搜索服务器。这当然是我的解决方案。这条路线提供了大量 Rails 本身没有的功能，可能有点矫枉过正，但值得考虑。

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

我使用 Sphinx 并使用 thinking-sphinx gem 实现它。

Resources:

资源：

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

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

http://sphinxsearch.com/

http://sphinxsearch.com/