It does not search non-string columns about textacular HOT 10 OPEN

I'd say beyond the scope of this gem. It's based on Postgres's native full test search capabilities, and so can do all and only all that those capabilities can do. If you know you're got a number, I think most DBs have pretty robust support for finding and matching.

Someone else may have a great idea, though, or a deeper insight.

from textacular.

Perhaps this is just another clarification, but I am finding lack of functionality depending on the column type again. While a column character varying(255) will give me a partial match using the fuzzy_search method, a column with data type text will not.

#Where description is datatype text in postgres

bicycle.description
=> 'blue with pink dots'

Bicycle.fuzzy_search('dots')
=> #<ActiveRecord::Relation []>

Bicycle/fuzzy_search('blue with pink dots')
=> #<ActiveRecord::Relation [#<Bicycle id: 6, date: "2011-03-05", city: "Vancouver", region: "WA", postal_code: "97212", serial: "ZC23R452", verified_ownership: false, police_report: "WA-1223043", description: "blue with pink dots", reward: 100, brand: "Rivendell", model: "Aquarius", color: "Seafoam Green", size: 54, size_type: "cm", created_at: "2013-10-23 21:15:20", updated_at: "2013-10-23 21:15:20", photo_file_name: nil, photo_content_type: nil, photo_file_size: nil, photo_updated_at: nil, country: "United States", user_id: 5, year: "2002", approved: false, hidden: false, recovered: false>]>

Why can't I partial match for datatype 'text'?

from textacular.

Can you share the SQL that this executes as in both cases? I'm not sure if this is to do with how Postgres's trigram search works or if it's something else. With the SQL, we can dive into the PG docs and maybe see what's what.

from textacular.

I'm having the same problem. Here's my sql:
I've got a model Article with columns title:string, content:text
Here's one record:

<Article id: 9, category_id: 14, title: "Success Stories title", content: "Mauris arcu purus, aliquet porta lobortis ut, place...", created_at: "2014-01-25 05:24:48", updated_at: "2014-01-25 05:24:48">

I get:

Article.fuzzy_search("Stories")
  Article Load (2.7ms)  SELECT "articles".*, similarity("articles"."title", 'Stories') + similarity("articles"."content", 'Stories') AS "rank0.28635084037190517" FROM "articles" WHERE (("articles"."title" % 'Stories') OR ("articles"."content" % 'Stories')) ORDER BY "rank0.28635084037190517" DESC
 => [#<Article id: 9, category_id: 14, title: "Success Stories title", content: "Mauris arcu purus, aliquet porta lobortis ut, place...", created_at: "2014-01-25 05:24:48", updated_at: "2014-01-25 05:24:48">]
Article.fuzzy_search("Mauris")
  Article Load (2.5ms)  SELECT "articles".*, similarity("articles"."title", 'Mauris') + similarity("articles"."content", 'Mauris') AS "rank0.3497130464447348" FROM "articles" WHERE (("articles"."title" % 'Mauris') OR ("articles"."content" % 'Mauris')) ORDER BY "rank0.3497130464447348" DESC
 => []

I'm no postgresql expert and I'm having trouble finding official documentation on the % operator. If I change the query to use ILIKE I get the expected results.

from textacular.

You know... I just had a thought (months later, I realize): This might have to do with similarity. If you fuzzy_search for "St" do you get your "Success Stories title"? It might not be similar enough to the string "St". I'm thinking that "Mauris" might be too small a sample for it to realize that they're the same thing?

Another thing might be that "Mauris" is Latin, but it's using English rules for stemming and such, so it might be breaking on that.

from textacular.

Is there a reason to use similarity instead of ilike? Would be nice if there was a way to use ilike.

from textacular.

I'm not a maintainer on this project any more, but I got the email, so what the hell.

AFAIK, ILIKE is a much more expensive operation than similarity(). Also, with similarity() you get things like stemming and such that real text search tools have, whereas ILIKE is much more literal.

from textacular.

@benhamill thanks for the the explanation!

from textacular.

Just came here to comment on this issue. I actually chose this gem over pg_search today as my first time playing with Postgres text search in Rails because of how simple it is. I like the convention... just look for all string columns and do a text search against each of them. But I do empathize with your needs here. May I offer a workaround?

1. Add a string column for every non-string column you want to search against. For example:

rails g model Post title body num_likes:integer num_likes_str

2. Use the before_save ActiveRecord callback to copy the value from the non-string field into the string field whenever the record is to be saved. For example:

class Post < ApplicationRecord
  before_save { self.num_likes_str = self.num_likes.to_s }
end

At that point, the model is now searchable because its num_likes_str column is now searchable. You don't need to actually display it in the view layer though, you can just forget it even exists and only render @post.num_likes etc.

from textacular.

@mattwelke also now you can use generated column for this on DB level.

Anyway we can mention in README that textacular works only for string columns and if you would like to search on different type, you need to type it to string first (for example with VIEW) or use different solution.

from textacular.