Doctrine2 findBy() order by joined table field

Question

I use Symfony2 with Doctrine2. There are the following entities:

/**
 * Person
 *
 * @ORM\Entity(repositoryClass="Acme\Bundle\ConsysBundle\Entity\PersonRepository")
 * @ORM\HasLifecycleCallbacks
 * @ORM\Table(name="person")
 */
class Person
{
    /**
     * @var integer
     *
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

    /**
     * 
     * @ORM\ManyToOne(targetEntity="Company", inversedBy="persons", fetch="EAGER")
     * @ORM\JoinColumn(name="company_id", referencedColumnName="id", onDelete="SET NULL")
     */
    private $company;

}

and

/**
 * Company
 *
 * @ORM\Entity(repositoryClass="Acme\Bundle\ConsysBundle\Entity\CompanyRepository")
 * @ORM\HasLifecycleCallbacks
 * @ORM\Table(name="company")
 */
class Company
{
    /**
     * @var integer
     *
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

    /**
     * @var string
     *
     * @ORM\Column(name="name", type="string", length=255, unique=true)
     */
    private $name;
}

Now in my controller I need to do the following:

    $repository = $this->getDoctrine()->getRepository('AcmeConsysBundle:Person');
    $people = $repository->findBy(
            array(),
            // here I want to order by the company name
            // that is taken from the Company entity
            array('company.name' => 'asc') 
    );

This code of course doesn't work, saying that "company.name" is unrecognized. But then how can I order by that column? Is it possible to do using findBy()?

Answer 1

add this to your PersonRepository

public function findAllOrderByCompany()
{
    return $this->createQueryBuilder('p')
        ->leftJoin('p.company','c')
        ->orderBy('c.name', 'asc')
        ->getQuery() 
        ->getResult();
}

If you want to sort an exisiting collection of Person entities by Company::getName

$iterator = $collection->getIterator();
$iterator->uasort(function ($a, $b) {
    return ($a->getCompany()->getName() < $b->getCompany()->getName()) ? -1 : 1;
});
$collection = new ArrayCollection(iterator_to_array($iterator));