无法使用教义DQL或QueryBuilder转换MySql查询 [英] Unable to convert MySql query using doctrine DQL or QueryBuilder

问题描述

我无法转换此查询:

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

进入DQL或使用QueryBuilder.

into DQL or using QueryBuilder.

我在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();

我得到了:

"[语法错误]行0，列216:错误:预期的Doctrine \ ORM \ Query \ Lexer :: T_CLOSE_PARENTHESIS，得到了'LIMIT'"

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

我尝试使用QueryBuilder:

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 :

[语义错误]第0行，'mc.id ORDER BY'附近的col 105:错误:'mc' 未定义

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

推荐答案

在这种情况下，Doctrine ORM可能会给您带来超出解决范围的问题.您可以:

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

1. 使用本地查询和相关的ResultSetMapping设置
2. 将SQL查询重构为Doctrine可以在DQL中处理的内容.查看查询，您可以采用多种方法(例如，将子查询视为FROM/JOIN部分中的临时表)，但我看不出Doctrine DQL允许的方式
3. 只需使用Doctrine DBAL进行直接SQL.看来您正在使用$this->_em，这使我认为您在EntityRepository中，因此您可以执行以下操作:$this->_em->getConnection()以获得

1. Use a native query and relevant ResultSetMapping setup
2. 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
3. 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.

我了解这些都不是理想的，但从经验来看，有时最好将Doctrine ORM推开，以实现您的需求.

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.

这篇关于无法使用教义DQL或QueryBuilder转换MySql查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！