Texticle fuzzy search not finding anything in Ruby on Rails

Question

I've added the following to my gemfile:

gem 'texticle', "2.0", :require => 'texticle/rails' # search gem

I then ran bundle install and bundle installed the gem

I'm using Rails 3.1.0 and I have am using a Postgres database.

I verify that I actually have the string I am looking for in the database table:

ruby-1.9.2-p290 :004 > Hotel.first
Hotel Load (0.4ms)  SELECT "hotels".* FROM "hotels" LIMIT 1
 => #<Hotel id: 1, title: "Marriot Hotel", created_at: "2012-03-01 23:53:16", updated_at: "2012-03-01 23:53:16">

When I run `Hotel.search('e')

ruby-1.9.2-p290 :005 > Hotel.search(:title => 'e')
Hotel Load (1.4ms)  SELECT "hotels".*, ts_rank(to_tsvector("hotels"."title"), to_tsquery('e')) AS "rank0.4785527956789428" FROM "hotels" WHERE (to_tsvector('english', "title") @@ to_tsquery('e')) ORDER BY "rank0.4785527956789428" DESC
 => []

I get nothing. I tried running Hotel.search('e') and still nothing. If I try Hotel.search(:title => 'Marriot') then it works but the reason I am using Texticle is for the fuzzy search.

Am I missing any other configurations?

Thanks

score 1 · Answer 1 · answered Sep 14 '12 at 13:58

There are a couple things I would suggest checking:

Verify an extension has been created manually for each database involved. I didn't have luck with 'texticle:install_trigram'. And if you're using Heroku, there may be additional steps, especially if you haven't migrated from your shared database.
Use the fuzzy_search method. In the latest version of Texticle, there are actually 3 new methods; the original #search method has been deprecated.

To manually install the extension:

Local Dev/Test

psql -U &lt;username>

\l
\c db_development
CREATE EXTENSION pg_trgm;
\dx

\c db_test
CREATE EXTENSION pg_trgm;
\dx

\q

Heroku Integration/Production

heroku pg:psql

\dx

CREATE EXTENSION pg_trgm;
\dx

More?

PostgreSQL Trigrams (fuzzy search)

Installing PostgreSQL Trigram

New Texticle search methods (source code)

texticle/lib/texticle.rb at master · texticle/texticle · GitHub

score 0 · Answer 2 · answered Aug 19 '12 at 15:27

My understanding of the way Texticle works is that it's going to do full text search on your string columns but not necessarily do fuzzy search by default. If you look at the query that it's generating in Postgres, it's looking for a match on 'e' rather than any word that contains the letter 'e'.

You can read the Postgres docs here:

http://www.postgresql.org/docs/9.1/static/datatype-textsearch.html

That said, I see support for prefix matching but not postfix in the docs, although I might be missing something.

What happens if you do Hotel.search('Marr:*') ?

Texticle fuzzy search not finding anything in Ruby on Rails

2 Answers2