Symfony2 - Custom Repository + Doctrine QueryBuilder = Errors?

Question

I have written a custom query in a repository:

public function findProductDetails($filter = array(), $offset = 0, $limit = 0)
{   
    $query = $this->getEntityManager()
            ->createQuery('SELECT prod, comp, cat FROM PaulDemoBundle:Product prod JOIN prod.category cat JOIN prod.company comp WHERE prod.productName LIKE \'%'.$filter['freetext'].'%\' ');
    $query->setFirstResult($offset);
    $query->setMaxResults($limit);

    return $query->getResult();
}

Now I am trying to add an ORDER BY into it, using the orderBy() part of the Doctrine Query Builder:

$query->orderBy('prod.productRef', 'ASC');

But I get errors when trying to run this:

Call to undefined method Doctrine\ORM\Query::orderBy()

So I added the following to the top of my repository, underneath the USE call for EntityRepository:

use Doctrine\ORM\Query;

But, no dice.

All documentation I have found simply shows the query being built using the $query->orderBy() method. No where in the Symfony2 documentation does it say how to set up your repo to work with the Doctrine Query Builder.

How on earth do I add the orderBy() method?

Am I even constructing my query properly? (Its based on the Symfony2 docs, but they are pretty poor tbh)

Answer 1

If you are not using the queryBuilder, you cannot use the methods applied to it. Either you do your query on your own, adding the ORDER BY to your Query like this:

->createQuery('SELECT prod, comp, cat FROM PaulDemoBundle:Product prod JOIN prod.category cat JOIN prod.company comp WHERE prod.productName LIKE \'%'.$filter['freetext'].'%\' ORDER BY prod.productRef ASC');

or you build your Query with the QueryBuilder:

$qb = $em->createQueryBuilder();
...

See here for the QueryBuilder and here for differences on createQuery and QueryBuilder.

Answer 2

If you use the QueryBuilder, the right method is addOrderBy