Doctrine - subquery in from

Question

I have a query in MySQL:

SELECT * FROM (
    SELECT COUNT(*) AS count, t.name AS name
    FROM tag t
    INNER JOIN video_has_tag v USING (idTag)
    GROUP BY v.idTag
    ORDER BY count DESC
    LIMIT 10
) as tags ORDER BY name

and I want to write this in doctrine. How I can do that? I wrote:

Doctrine_Query::create()
        ->select('COUNT(t.idtag) as count, t.name')
        ->from('Tag t')
        ->innerJoin('t.VideoHasTag v')
        ->groupBy('v.idTag')
        ->orderBy('count DESC, t.name')
        ->limit(30)
        ->execute();

But I can't put it in "from" to sort by name.

Answer 1

This is a answer:

$q = new Doctrine_RawSql();
$q->addComponent('t', 'Tag')
    ->select('{t.name}, {t.count}')
    ->from('(SELECT COUNT(*) as count, t.name,t.idtag
        FROM Tag t
            INNER JOIN Video_Has_Tag v USING(idTag)
        GROUP BY v.idTag
        ORDER BY count DESC
        LIMIT 50) t')
    ->orderBy('name');

Answer 2

I use Doctrine 1.2 and Symfony 1.4 and Doctrine_RawSql works with subqueries. You can then addComponent for models.

Something worth mentioning is that you can't directly use Doctrine_Query objects as subqueries BUT you can easily get the SQL form via getSqlQuery() method or even getSqlQueryPart and use the result for the subquery.

getSqlQueryPart is useful in rebuilding only certain parts of a query.

Answer 3

Doctrine cannot do a subquery in the FROM clause (nor can it JOIN to a subquery). Your current Doctrine query is going to sort by count and then by name. Is that not what you are wanting?