Can not define procedure

[Slavenin]

Hy! For full text search in ms sql i use procedure FREETEXT . I define function FREETEXT and added it in config.yml

<?php
namespace Sozd\SozdBundle\Doctrine;

use Doctrine\ORM\Query\AST\Functions\FunctionNode;
use Doctrine\ORM\Query\Lexer;
use Doctrine\ORM\Query\AST\PathExpression;
use Doctrine\ORM\Query\AST\InputParameter;

class FreetextFunction extends FunctionNode
{
    /**
     * @var PathExpression
     */
    private $field;

    /**
     * @var InputParameter
     */
    private $text;

    /**
     * @param \Doctrine\ORM\Query\SqlWalker $sqlWalker
     *
     * @return string
     */
    public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker)
    {
        return 'FREETEXT(' . $this->field->dispatch($sqlWalker) . ', ' . $this->text->dispatch($sqlWalker) . ')';
    }

    /**
     * @param \Doctrine\ORM\Query\Parser $parser
     *
     * @return void
     */
    public function parse(\Doctrine\ORM\Query\Parser $parser)
    {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);
        $this->field = $parser->StateFieldPathExpression();
        $parser->match(Lexer::T_COMMA);
        $this->text = $parser->InParameter();
        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
    }
}

But when i use it in qb

$q = $this
            ->getDoctrine()
            ->getManager()
            ->getRepository('Entity')
            ->createQueryBuilder('fd')
            ->where('FREETEXT(fd.plaintext, :text)')
            ->setParameter('text', $rq)
        ;

rise exception:

[Syntax Error] line 0, col -1: Error: Expected =, <, <=, <>, >, >=, !=, got end of string.

becose expected something like ->where('FREETEXT(fd.plaintext, :text) = 1') but in this case ms sql rise sql exception becose FREETEXT is procedure and not return value.

How define procedure in doctrine and use it in where clause?

[Ocramius]

If it's a procedure (and it returns no value), so why is it used in a WHERE clause? Surely that would cause your WHERE condition to always evaluate to FALSE/NULL, and therefore you'd have no results?

Marco Pivetta

http://twitter.com/Ocramius

http://ocramius.github.com/

On 26 February 2016 at 04:17, Max notifications@github.com wrote:

Hy! For full text search in ms sql i use procedure FREETEXT https://technet.microsoft.com/ru-ru/library/ms176078(v=sql.105).aspx. I define function FREETEXT and added it in config.yml

<?phpnamespace Sozd\SozdBundle\Doctrine;use Doctrine\ORM\Query\AST\Functions\FunctionNode;use Doctrine\ORM\Query\Lexer;use Doctrine\ORM\Query\AST\PathExpression;use Doctrine\ORM\Query\AST\InputParameter;class FreetextFunction extends FunctionNode{ /* * @var PathExpression / private $field; /* * @var InputParameter / private $text; /* * @param \Doctrine\ORM\Query\SqlWalker $sqlWalker * * @return string / public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker) { return 'FREETEXT(' . $this->field->dispatch($sqlWalker) . ', ' . $this->text->dispatch($sqlWalker) . ')'; } /* * @param \Doctrine\ORM\Query\Parser $parser * * @return void / public function parse(\Doctrine\ORM\Query\Parser $parser) { $parser->match(Lexer::T_IDENTIFIER); $parser->match(Lexer::T_OPEN_PARENTHESIS); $this->field = $parser->StateFieldPathExpression(); $parser->match(Lexer::T_COMMA); $this->text = $parser->InParameter(); $parser->match(Lexer::T_CLOSE_PARENTHESIS); }}

But when i use it in qb

$q = $this ->getDoctrine() ->getManager() ->getRepository('Entity') ->createQueryBuilder('fd') ->where('FREETEXT(fd.plaintext, :text)') ->setParameter('text', $rq) ;

rise exception:

[Syntax Error] line 0, col -1: Error: Expected =, <, <=, <>, >, >=, !=, got end of string.

becose expected something like ->where('FREETEXT(fd.plaintext, :text) = 1') but in this case ms sql rise sql exception becose FREETEXT is procedure and not return value.

How define procedure in doctrine and use it in where clause?

— Reply to this email directly or view it on GitHub https://github.com/doctrine/doctrine2/issues/5681.

[Slavenin]

Perhaps, FREETEXT return value, but it can not be compare. Exmple query from docs:

SELECT Description 
FROM Production.ProductDescription 
WHERE FREETEXT(Description, @SearchWord);

If add any comparison expression will be syntax error! How i can execute this qery using dql?

[Ocramius]

If this is due to an engine limitation, I suggest just writing the query in raw SQL then.

[Slavenin]

That's what I did, but raw sql not good, difficult to use pagination etc. Why not change this behavior? For "exists" expression this work, why not make for other?

[Ocramius]

Raw SQL is perfectly fine for edge cases like these. The behavior can be improved, by allowing DQL to accept a ConditionalPrimary that is just a function call. What do you think, @guilhermeblanco?