symfony - Doctrine: ordering By Count on Associated entities -

goal: ordering entities on how ofter used on associated relations (onetomany)

the problem: dql, somehow handles keyword entity or class.

the exception thrown: queryexception: [semantical error] line 0 col 103 near 'from mybundle:entity error: class 'from' not defined.

---

here sql query wrote test how data. works perfectly

select en.id, (count(distinct ag.artist_id) + count(distinct rg.release_id) + count(distinct tg.track_id)) total  myapp.entity en  left join myapp.other_one o1 on o1.entity_id = en.id left join myapp.other_two o2 on o2.entity_id = en.id  group en.id  order total desc ; 

---

to data in symfony hydrate objects try use doctrine query language in entityrepository this:

/**  * find common entities  *   * @return array  */ public function findmostcommon() {     $em = $this->getentitymanager();     $qb = $em->createquerybuilder();      $qb         ->select('en, (count(distinct en.other1) + count(distinct en.other2)) count')         ->from('marulabundle:entity', 'en')         ->leftjoin('mybundle:other1', 'o1', 'with', 'o1.entity = en.id')         ->leftjoin('mybundle:other2', 'o2', 'with', 'o2.entity = en.id')         ->groupby('en')         ->orderby('count', 'desc')     ;      return $qb->getquery()->getresult(); } 

---

since possible in sql query. hoping work fine dql.

has experienced error before? possible achieve doctrine, or doctrine limited relating issue?

oops: should not use keyword "count"

solution:

->select('en, (count(distinct en.other1) + count(distinct en.other2)) hidden ordercount') 

note: adding hidden keyword helps entities back, makes hydration fit right in