wildcard in fulltext mode

[ghazel]

When searching without fulltext (and :left_wildcard => false) the query is constructed as field LIKE 'foo%'. However in fulltext mode, the query is MATCH(table.field) AGAINST('+foo' IN BOOLEAN MODE), which looks for exact matches instead of a wildcard type search, which would be +foo*.

So, it seems like fulltext query elements should have * appended automatically.

[mrkamel]

Hi, thanks for reporting. Just trying to reproduce, but my test case is running fine:

  def test_wildcard
    expected = create(:product, :title => "Expected")
    rejected = create(:product, :title => "Rejected")

    results = Product.search("title:Expec*")

    assert_includes results, expected
    refute_includes results, rejected
  end 

and generates

... WHERE (MATCH(`products`.`title`) AGAINST('Expec*' IN BOOLEAN MODE))

Could you please provide the full search cop model layout, search cop query and sql query generated?

Thanks in advance
Benjamin

[ghazel]

In your test case you've explicitly passed * to the search function. If the search cop model used LIKE instead, you wouldn't need to add the *, it would automatically append a %.

[mrkamel]

Ah, ok. However, this behaviour is desired. Fulltext search usually works this way and if you don't want it, simply do not use the fulltext feature.

[ghazel]

fulltext has the benefit of finding any word with that prefix though, right? LIKE '%foo' only works on the first word.

[mrkamel]

That's true. However, the use cases seem to be limited to me. Maybe you can give examples. I'd be ok, though, with an option option :title, :fulltext => true, :fulltext_wildcard => true or similar to opt this feature in.

[ghazel]

That feature would work great for me. I'm a little skeptical about splitting on spaces and adding asterisks -- probably there should be escaping or something.

My use case is simple keyword (prefix) search, where LIKE word work but do a full table scan.

[mrkamel]

Do you feel like creating a PR?

[aaronchi]

This would be nice to have. It's available in pg_search

[mrkamel]

I've added this feature in branch 23_fulltext_right_wildcard.
You're very welcome to try it out already.