Doctrine 中带有 LIMIT 的子查询

问题描述

我正在尝试执行一个带有 Doctrine 子查询的查询.现在它给了我一个错误.我在存储库中的功能是:

I'm trying to do a query that has a subquery with Doctrine. Right now it's giving me an error. My function in the repository is:

public function getRecentPlaylists($count = 3) {


    $q = $this->_em->createQuery("
            SELECT p.id,
            p.featuredImage,
            p.title,
            p.slug,
            a.firstName,
            a.lastName,
            a.slug as authorSlug,
            (SELECT updated
                     FROM EntitiesArticles
                     ORDER BY updated DESC LIMIT 1) as updated
            FROM EntitiesPlaylist p
            JOIN EntitiesAccount a
                        ON p.account_id = a.id
            ")
        ->setMaxResults($count);

            try{
            return $q->getResult();
            }catch(Exception $e){
                echo $e->message();
            }

}

这给了我这个错误:

[Semantical Error] line 0, col 210 near 'LIMIT 1) as updated FROM': Error: Class 'LIMIT' is not defined.

我几乎要放弃 Doctrine，我一直无法弄清楚如何使用子查询或联合与子查询进行查询.对这个功能有帮助吗?谢谢！

I'm almost giving up on Doctrine, I haven't been able to figure out how to do queries with subqueries or unions with subqueries. Any help with this function? Thanks!

推荐答案

你需要的是取出内部查询，单独制作 DQL，然后在里面使用生成的 DQL

What you need is to take out the inner query and make the DQL separately for that, then use the generated DQL inside

$inner_q = $this->_em
    ->createQuery("SELECT AR.updated FROM EntitiesArticles AR ORDER BY AR.updated DESC")
    ->setMaxResults(1)
    ->getDQL();

$q = $this->_em->createQuery("SELECT p.id,
        p.featuredImage,
        p.title,
        p.slug,
        a.firstName,
        a.lastName,
        a.slug as authorSlug,
        (".$inner_q.") AS updated
        FROM EntitiesPlaylist p
        JOIN EntitiesAccount a
             ON p.account_id = a.id
    ")
    ->setMaxResults($count);
try{
    return $q->getResult();
}
catch(Exception $e){
    echo $e->message();
}

这篇关于Doctrine 中带有 LIMIT 的子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！