Doctrine2: DQL with parameters

Question

I'd like to have sorting type as a parameter. So I wrote function

public function findInterval($pageNumber, $limit, $sortType) {
    $query = $this->_em->createQuery('Select c from Entities\Comment c where c.isremoved=0 ORDER BY c.creationdate ?1');
    $query->setParameter(1, $sortType);  //sortType is either ASC or DESC

    return $users = $query->getResult();
}

But it doesn't work with fatal error Uncaught exception 'Doctrine\ORM\Query\QueryException' with message '[Syntax Error] line 0, col 77: Error: Expected end of string, got '?'' in C:\Users\user\Desktop\projects\interview\application\libraries\Doctrine\ORM\Query\QueryException.php:42 Stack trace: #0 C:\Users\user\Desktop\projects\interview\application\libraries\Doctrine\ORM\Query\Parser.php(380): Doctrine\ORM\Query\QueryException::syntaxError('line 0, col 77:...') #1 C:\Users\user\Desktop\projects\interview\application\libraries\Doctrine\ORM\Query\Parser.php(745): Doctrine\ORM\Query\Parser->syntaxError('end of string') #2 C:\Users\user\Desktop\projects\interview\application\libraries\Doctrine\ORM\Query\Parser.php(213): Doctrine\ORM\Query\Parser->QueryLanguage() #3 C:\Users\user\Desktop\projects\interview\application\libraries\Doctrine\ORM\Query\Parser.php(288): Doctrine\ORM\Query\Parser->getAST() #4 C:\Users\user\Desktop\projects\interview\application\libraries\Doctrine\ORM\Query.php(230): Doctrine\ORM\Query\Parser->parse() #5 C:\Users\user\Deskt in C:\Users\user\Desktop\projects\interview\application\libraries\Doctrine\ORM\Query\QueryException.php on line 42

Is there any other way how to set sort type by parameter?

Answer 1

You only can bind parameters (used in where) in prepared statements. There is no need to anyway to use this in orderBy because there is no possibility for SQL injection on that part.

Just concat using plain PHP:

$sortType = ($sortType == 1) ? 'ASC' : 'DESC';
$query = $this->_em->createQuery('Select c from Entities\Comment c where c.isremoved=0 ORDER BY c.creationdate ' . $sortType);