Use "to_tsquery" instead for partial matching

[hotmeteor]

By using to_tsquery you can enable partial matching on words.

Also, as a separate feature, the ability to have & between separate terms would allow for partial matching on multiple terms. The ranking is still maintained, so you still get good results.

[tortuetorche]

If I run a search with space (e.g. foo bar, PostgreSQL returns a syntax exception:

You can reproduce my issue with this query:

SELECT to_tsquery(COALESCE('english', get_current_ts_config()), 'foo bar');

[hotmeteor]

That could be resolved with a filter on the terms and just going ahead with an & addition to this type of query.

I'm wondering if this is really a new config setting, something like:

return [
    'allow_partial' => true
];

If set to false it uses plainto_tsquery. If set to true uses to_tsquery, and filters and parses the search terms with a method such as:

return collect(array_filter(explode(' ', preg_replace("/[^0-9a-zA-Z ]/", " ", $term))))->map(function ($t) {
    return $t.':*';
})->implode(' & ');

Is this idea worth putting together a larger PR for? I know I would find it extremely useful.

[pmatseykanets]

Good idea! @hotmeteor and @tortuetorche thanks.

I'm thinking about extracting the tsquery building logic into it's own Query or TsQuery class and making the Engine to expect a properly formed tsquery. This way one would not be limited just to to_tsquery and plainto_tsquery because there are other ways to build more sophisticated text search queries.

This requires a breaking change but I don't another way around it since Scout Builder accepts only a string in query property.

Something like (pseudocode)

$tsquery = TsQuery::create('fat', 'english')
    ->and('rat')
    ->followedBy('cat', 10)
    ->get();

App\Post::search($tsquery)
   ->where(...)
   ->paginate(15)

Thoughts?

[hotmeteor]

@pmatseykanets I think that's an excellent idea. I also don't think it would be difficult to also provide a trgm option for trigram fuzzy search.

Happy to be involved if needed.

[tortuetorche]

@pmatseykanets If we can do what you suggest:

$tsquery = TsQuery::create('fat', 'english')
    ->and('rat')
    ->followedBy('cat', 10)
    ->get();

App\Post::search($tsquery)
   ->where(...)
   ->paginate(15)

And also a classic: search:

$term = "foo bar";
App\Post::search($term)
   ->where(...)
   ->paginate(15)

It's win win 😸

[tortuetorche]

@hotmeteor Do you know how to mix trigram and full text search (with weights) in only one query? For purpose, I have a ['lastname' => 'A', 'firstname' => 'B'] and I want to make a fuzzy search on this terms. Actually, I mix the results generated by the Laravel Scout Postgres plugin and a manual trigram query.

[hotmeteor]

@tortuetorche This library is just an engine for Scout and I don't think it can change how Scout interacts with Eloquent. I agree that Scout should be operate more like a scope on a classic search, but that would be difficult because it requires ranking the results as well as text matching.

Doing a trigram search requires adding a different index type than you do for a tsquery search. See https://www.postgresql.org/docs/9.0/static/pgtrgm.html

[lbm-trentm]

Did this pull request end up going anywhere?

[salvisb]

I would also appreciate this functionality.

[pmatseykanets]

@lbm-trentm Thanks for showing the interest. If all goes well I'm going to work on this pretty soon.

[matevisky]

Any update on this, it would be nice to merge...?

[mk-conn]

Was thinking the same, would be cool if this is gonna be merged :)

[pmatseykanets]

Please see #20