Missing PlainToTsquery

[allan-simon]

currently with ToTsquery, it fails if you enter a string with a space , without a and for this postgresql propose the plainto_tsquery which is not supported by this library.

[nepster-web]

I also received a similar problem.

[ilyar]

@allan-simon @nepster-web Please give an example

[allan-simon]

It's been a long time so I don't remember very well , but basically in the code below if you use ToTsquery and you search for an author name "Victor Hugo" for example it will fail and you need to type "Victor and hugo" (something like that)

        return $builder
            ->select('a')
            ->from('AppBundle:Author', 'a')
            // we concat title and description as part of the ts_vector
            // that we used to match on the ts_query
            // see https://www.postgresql.org/docs/9.5/static/textsearch-tables.html
            ->where(
                "
                TS_MATCH_OP(
                    TO_TSVECTOR(
                        'fr',
                        TS_CONCAT_OP(a.firstName, ' ', a.lastName)
                    ),
                    PLAINTO_TSQUERY('fr', :query)
                ) = true
                "
            )
            ->setParameter('query', $query)
            ->getQuery()
            ->getResult()
        ;

(note in the example above i added myself a plain_to_tsquery operator like this :+1:

<?php

namespace AppBundle\ORM;

use Doctrine\ORM\Query\AST\Functions\FunctionNode;
use Doctrine\ORM\Query\Lexer;
use Doctrine\ORM\Query\Parser;
use Doctrine\ORM\Query\SqlWalker;

class PlainToTsquery extends FunctionNode
{
    private $lang;
    private $text = null;

    public function parse(Parser $parser)
    {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);
        $this->text = $parser->StringExpression(); //SingleValuedPathExpression();
        // parse second parameter if available
        $lexer = $parser->getLexer();
        if(Lexer::T_COMMA === $lexer->lookahead['type']){
            $parser->match(Lexer::T_COMMA);
            // then actually the first argument was
            // the language
            $this->lang = $this->text;
            $this->text = $parser->StringExpression();
        }
        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
    }

    public function getSql(SqlWalker $sqlWalker)
    {
        if (is_null($this->lang)) {
            return sprintf(
                'plainto_tsquery(%s)',
                $this->text->dispatch($sqlWalker)
            );
        }
        return sprintf(
            'plainto_tsquery(%s::regconfig, %s)',
            $this->lang->dispatch($sqlWalker),
            $this->text->dispatch($sqlWalker)
        );
    }
}