Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL Full-text search in Ruby on Rails

I am trying to implement a basic full-text search with MySQL.

I wrote this migration:


def self.up
  execute 'ALTER TABLE photos ENGINE = MyISAM'
  execute 'CREATE FULLTEXT INDEX fulltext_photos ON photos (place, info)'
end

def self.down
  execute 'ALTER TABLE photos ENGINE = InnoDB'
  execute 'DROP INDEX fulltext_photos ON photos'
end

And here's my model:


def self.search(*args)
  options = args.extract_options!
  find_by_sql [ "SELECT * FROM photos WHERE MATCH (place, info) AGAINST (?)", options[:query] ]
end

The problem is that this code always returns an empty array.

For example:

% Photo.find(:first)
=> Photo id: 1, place: "Baceno", info: "Era immerso in erba alta." ... 

% Photo.search(:all, :query => 'baceno')
=> []
like image 774
collimarco Avatar asked Jun 24 '09 16:06

collimarco


1 Answers

I created a project (Rails 2.3.2, Ruby 1.9.1 MySQL 5.0) to emulate this. With one record in the database, I got the same results you did. When I added more records, the Photo.search command found the record.

This could be because "words that are present in 50% or more of the rows are considered common and do not match". Ref.

The 50% threshold does not apply in binary mode. Ref.

IN BINARY MODE belongs inside the parentheses: AGAINST ('baceno' IN BOOLEAN MODE)

like image 90
user89447 Avatar answered Sep 26 '22 12:09

user89447