php - Unable to convert MySql query using doctrine DQL or QueryBuilder -

i cannot convert query :

select c.title, count(*), (     select ba_thumb.link     ba_video     inner join video_channel on video_channel.video_id=ba_video.id     inner join ba_thumb on ba_thumb.video_id=video_channel.video_id     inner join ba_channel on ba_channel.id=video_channel.channel_id     video_channel.channel_id=c.id     order ba_video.views desc, ba_thumb.id asc     limit 1 ) ba_thumb_link ba_channel c inner join video_channel on video_channel.channel_id=c.id inner join ba_video on ba_video.id=video_channel.video_id group video_channel.channel_id order count(*) desc 

into dql or using querybuilder.

i tried in dql :

return $this->_em->createquery('             select c.title, count(*),              (                 select t.link                 bavideogallerybundle:video v                 inner join v.channels c                  inner join v.thumbs t                  c.id=mc.id                 order v.views desc, t.id asc                 limit 1              )              bavideogallerybundle:channel mc              inner join bavideogallerybundle:video mv              group mv.channels.id              order count(*) desc')              ->getresult(); 

i :

"[syntax error] line 0, col 216: error: expected doctrine\orm\query\lexer::t_close_parenthesis, got 'limit' "

and tried using querybuilder :

$query = $this->getentitymanager()->createquerybuilder()     ->select('c.title, count(*)')     ->from('bavideogallerybundle:channel', 'mc')     ->innerjoin('bavideogallerybundle:video', 'mv')     ->groupby('mv.channels.id')     ->orderby('count(*)', 'desc');  $subquery = $this->getentitymanager()->createquerybuilder()     ->select('t.link')     ->from('bavideogallerybundle:video', 'v')     ->innerjoin('v.channels', 'c')     ->innerjoin('v.thumbs', 't')     ->where('c.id=mc.id')     ->orderby('v.views', 'desc')     ->orderby('t.id', 'asc')     ->getquery()     ->getresult();  return $query->addselect('('.$subquery->getdql().')') 

but correlation not working, :

[semantical error] line 0, col 105 near 'mc.id order by': error: 'mc' not defined

this 1 of situations doctrine orm going cause more issues solves. could:

1. use native query , relevant resultsetmapping setup
2. refactor sql query doctrine can handle in dql. looking @ query have there number of different ways (e.g. treating subquery temporary table in / join part) can't see of way doctrine dql allow
3. just go straight sql using doctrine dbal. looks you're using $this->_em makes me think you're in entityrepository, do: $this->_em->getconnection() dbal connection $conn->query(). way lose benefits of orm (database agnostic etc.) can think of orms having tax executing complex queries.

i understand none of these ideal experience, it's better push doctrine orm out of way achieve need.