Symfony3 QueryBuilder orderBy entity field

Question

I'm building a query as an EntityRepository with querybuilder. I have a ManyToOne relationship (by the ID) im trying to order the query by a value of the relationship instead. Code snippets below.

namespace AppBundle\Entity\Repository;
class ContratoRepository extends EntityRepository
{

    public function getWithoutParams($ops)
    {

        $query=$this->createQueryBuilder('e')
            ->orderBy('e.proc','ASC');
        return $query->getQuery()->getResult();
    }

Now my entity

namespace AppBundle\Entity;
use Doctrine\ORM\Mapping as ORM;
/**
 * @ORM\Table()
 * @ORM\Entity(repositoryClass="AppBundle\Entity\Repository\ContratoRepository")
 */

    class Contrato
    {
...
     /**
         * @ORM\ManyToOne(targetEntity="AppBundle\Entity\ContratosAdmin\Proc")
         * @ORM\JoinColumn(name="id_proc", referencedColumnName="id_proc")
         * @ORM\OrderBy({"name" = "ASC"})
         **/
        private $proc;

Now my question is, is it possible to order by the "name" field inside my Proc Entity ? as it is now, it's ordering by it's Id.

Thank you, Regards.

Try to make a join request in the ContratoRepository to retrieve the data of Contrato AND Proc entity. then in your queryBuilder request you will have something like ->orderBy('p.name','ASC'); where p represent your Proc entity. — Grechka Vassili

Joe Joe · Accepted Answer · 2017-05-17T11:47:04

It should be possible according to the doctrine documentation: http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/tutorials/ordered-associations.html

But according to this you'll have to adjust your query a bit otherwise the orderBy Annotation won't be included in the query.

Try this query:

public function getWithoutParams($ops)
    {
        $query=$this->createQueryBuilder('e')
               ->select('e, p')
               ->leftJoin('e.proc', 'p');

        return $query->getQuery()->getResult();
    }

According to the docs the orderBy should be added automatically in this case.

For a use without the Annotation just join the relation and add a manual orderBy like Grechka mentioned.

Symfony3 QueryBuilder orderBy entity field

1 Answers