Unable to convert MySql query using doctrine DQL o

I cannot convert this query :

SELECT c.title, COUNT(*),
(
    SELECT ba_thumb.link
    FROM 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
    WHERE video_channel.channel_id=c.id
    ORDER BY ba_video.views DESC, ba_thumb.id ASC
    LIMIT 1
) AS ba_thumb_link
FROM 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 BY video_channel.channel_id
ORDER BY COUNT(*) DESC

into DQL or using QueryBuilder.

I tried in DQL :

return $this->_em->createQuery('
            SELECT c.title, COUNT(*),
             (
                SELECT t.link
                FROM BAVideoGalleryBundle:Video v
                INNER JOIN v.channels c 
                INNER JOIN v.thumbs t 
                WHERE c.id=mc.id
                ORDER BY v.views DESC, t.id ASC
                LIMIT 1
             )
             FROM BAVideoGalleryBundle:Channel mc
             INNER JOIN BAVideoGalleryBundle:Video mv
             GROUP BY mv.Channels.id
             ORDER BY COUNT(*) DESC')
             ->getResult();

I get :

"[Syntax Error] line 0, col 216: Error: Expected Doctrine\ORM\Query\Lexer::T_CLOSE_PARENTHESIS, got 'LIMIT' "

And i 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 is not working, i get :

[Semantical Error] line 0, col 105 near 'mc.id ORDER BY': Error: 'mc' is not defined

This is one of those situations where Doctrine ORM is likely going to cause you more issues than it solves. You could:

Use a native query and relevant ResultSetMapping setup
Refactor your SQL query into something that Doctrine can handle in DQL. Looking at the query you have there are a number of different ways you could do it (e.g. treating the subquery as a temporary table in the FROM / JOIN part) but I can't see of a way that Doctrine DQL would allow
Just go for straight SQL using Doctrine DBAL. It looks like you're using $this->_em which makes me think you're in an EntityRepository, so you could do: $this->_em->getConnection() to get a DBAL connection then just do $conn->query(). Obviously this way you lose the benefits of an ORM (database agnostic etc.) but you can think of ORMs as having a tax executing complex queries.

I understand none of these are ideal but from experience, sometimes it's better to push Doctrine ORM out of the way to achieve what you need.