CODEWITHSUNDEEP
laravelpostgresql
Beusebiu
Beusebiu

Reputation: 1513

Full text search with Laravel and Postgresql

I try to do a full text search, but I got stuck in a error.

Migration:

public function up()
{
    Schema::create('lkp_answers', function (Blueprint $table) {
        $table->id();
        $table->string('name');
        //code...
    });

    DB::statement("ALTER TABLE lkp_answers ADD COLUMN searchtext TSVECTOR");
    DB::statement("UPDATE lkp_answers SET searchtext = to_tsvector('english', name )");
    DB::statement("CREATE INDEX searchtext_gin ON lkp_answers USING GIN(searchtext)");
    DB::statement("CREATE TRIGGER ts_searchtext BEFORE INSERT OR UPDATE ON lkp_answers FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger('searchtext', 'pg_catalog.english', 'name')");
}

public function down()
{
    DB::statement("DROP TRIGGER IF EXISTS tsvector_update_trigger ON lkp_answers");
    DB::statement("DROP INDEX IF EXISTS searchtext_gin");
    DB::statement("ALTER TABLE lkp_answers DROP COLUMN searchtext");
    Schema::dropIfExists('lkp_answers');
}

Model

class LkpAnswer extends Model
{
    public function scopeSearch($query, $search)
    {
        if (!$search) {
            return $query;
        }
        return $query->whereRaw('searchtext @@ to_tsquery(\'english\', ?)', [$search])
            ->orderByRaw('ts_rank(searchtext, to_tsquery(\'english\', ?)) DESC', [$search]);
    }
}

Controller

$title = 'RETENTION OF TITLE';
$topics = LkpAnswer::search($title)->get();

Error:

SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error in tsquery: "RETENTION OF TITLE" (SQL: select * from "lkp_answers" where searchtext @@ to_tsquery('english', RETENTION OF TITLE) order by ts_rank(searchtext, to_tsquery('english', RETENTION OF TITLE)) DESC)"

Upvotes: 2

Views: 2444

Answers (1)

Beusebiu
Beusebiu

Reputation: 1513

I found out the answer. In controller I have to replace to_tsquery with plainto_tsquery, and it works. Because I use a string from application is better to use plainto_tsquery because it takes plain string and It assumes that is no operation.

Upvotes: 0

Related Questions