[SOLVED] symfony2 querybuilder orderby count manytomany

symfony2 querybuilder orderby count manytomany

I have an entity called School, it has a ManyToMany relation "methods"

class School{
 /**
 * @ORM\Id
 * @ORM\GeneratedValue(strategy="AUTO")
 * @ORM\Column(type="integer")
 */
protected $id;

/**
 * @ORM\ManyToMany(targetEntity="Method", inversedBy="schools")
 * @ORM\JoinTable(name="lk_access_method")
 * @ORM\OrderBy({"name" = "asc"})
 */
protected $methods;
}

Now I want to write a createQueryBuilder that orders by de count "methods"

Something like:

$schools = $this->createQueryBuilder('s')
            ->select("s")
            ->orderBy("COUNT(s.methods)")
            ->addOrderBy("s.name")
            ->setMaxResults($count)
            ->setFirstResult($pos)
            ->getQuery()
            ->getResult();

But that didn't work... anybody has a better idea?

Solution

Try to add join

->join('s.methods', 'm')
->orderBy("COUNT(m.id)")

EDIT.

->addSelect('COUNT(m.id) as nMethods')
->join('s.methods', 'm')
->groupBy('s.id')
->orderBy("nMethods", 'DESC')