mrkamel
wildcard in fulltext mode
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.
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
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 %.
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.
fulltext has the benefit of finding any word with that prefix though, right? LIKE '%foo' only works on the first word.
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.
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.
I've added this feature in branch 23_fulltext_right_wildcard. You're very welcome to try it out already.