Symfony - Create query error

Question

I am using a custom FindBy filter where I want to pass orderBy and orderDirection as parameters like this:

public function findByFilter($filter, $orderBy = null, $orderDirection = null)
{
 ...

 return $this->getEntityManager()->createQuery(
        'SELECT i FROM ' . $entityName . ' i ' .
        'WHERE i.id LIKE :id'
        . ($orderBy) ? ' ORDER BY i.' . $orderBy : ''
        . ($orderDirection) ? ' ' . $orderDirection : ''
    )
        ->setParameter('id', $filter)
        ->getResult()
    ;
 }

I am getting the following error message:

[Syntax Error] line 0, col 1: Error: Expected SELECT, UPDATE or DELETE, got 'ASC'

Markus Kottländer · Accepted Answer · 2014-12-03 10:36:33Z

2

Try this:

return $this->getEntityManager()->createQuery(
        'SELECT i FROM ' . $entityName . ' i ' .
        'WHERE i.id LIKE :id '
        . ($orderBy ? ' ORDER BY i.' . $orderBy : '')
        . ($orderDirection ? ' ' . $orderDirection : '')
    )
        ->setParameter('id', $filter)
        ->getResult()
    ;
}

You were missing a space after ... :id and the complete ternary operator has to be inside the braces. Also There is a orderBy method:

public function findByFilter($filter, $orderBy = null, $orderDirection = null)
{
    ...

    $query = $this->getEntityManager()->createQuery($entityName . ' i')
        ->where('i.id LIKE :id')
        ->setParameter('id', $filter);

    if ($orderBy && $orderDirection) {
        $query->orderBy('i.' . $orderBy . ' ' . $orderDirection);
    }
    return $query->getResult();
}

Code not tested but should work somehow this way. More info: https://github.com/doctrine/doctrine2/blob/master/lib/Doctrine/ORM/EntityManager.php#L287 and https://github.com/doctrine/doctrine2/blob/master/lib/Doctrine/ORM/Query.php

UPDATE: Ok sorry, I figured out that there is of course a slight difference between Query and queryBuilder. Query seems to be lacking the orderBy method.

edited Dec 3, 2014 at 10:36

answered Dec 3, 2014 at 10:14

Markus Kottländer

8,2844 gold badges42 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Herr Nentu' Over a year ago

You answer needs a bit of changes but it helped me out solving it. I will accept it as the right answer. The query should be like this $query = $this->_em->getRepository($entityName)->createQueryBuilder(' i ') and everything else is like your second part of the answer. Thanks

Collectives™ on Stack Overflow

Symfony - Create query error

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related