Doctrine - subquery in from

Question

I have 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

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?

Answer 2

Doctrine will let you put a subquery in the FROM clause. However, it only can take DQL text statements in the subquery, you can't actually use another query object. If you rewrite your DQL subquery in textual form, you should be able to use it.

See this page of the documentation for an example. The example puts the DQL subquery in the WHERE clause, but it mentions that you can use subqueries in the FROM clause.

Answer 3

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