Doctrine - subquery in from

问题:

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.

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

回答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.

回答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?

回答4:

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.