symfony2 querybuilder orderby count manytomany

Question

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?

Alexey B. · Accepted Answer · 2013-07-25T08:03:42.957

12

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')

edited Jul 25 '13 at 08:03

answered Jul 23 '13 at 14:53

Alexey B.

11,965
2
49
73

isn't working... my error: [Syntax Error] line 0, col 106: Error: Expected end of string, got '(' on the line where says COUNT( – Lisa Van Daele Jul 25 '13 at 07:13
Yes that works fine! Thanks! but now I got an 2 dimensional array of the school entity and the nMethods. I make a new array in a foreach loop with only the entity School in it, or is there a better way. – Lisa Van Daele Jul 25 '13 at 10:27
1

@LisaVanDaele you can use `AS HIDDEN` to get rid of the extra field in the results: `COUNT(m.id) AS HIDDEN nMethods`. This will work. – Andrzej Ośmiałowski Feb 12 '14 at 16:45
With the default join, if an item have no relations it won't be returned. to return also items with no relations (where count is 0) use leftjoin() instead of join() – Guillem Cucurull Nov 10 '15 at 12:33

symfony2 querybuilder orderby count manytomany

1 Answers1