Doctrine JOIN 与 DQL 中的子查询

我有这个 SQL:

SELECT
s.id,
e.exception,
s.name,
w.url,
w.web_id,
w.active,
w.suspended,
r.email,
p.name as partner,
p.id as partnerId,
group_concat(c.name) names,
group_concat(c.email) emails,
group_concat(c.tel) tels
FROM
service s
JOIN web w ON s.web_id = w.id
JOIN rus r ON w.rus_id = r.id
JOIN partner p ON r.partner_id = p.id
LEFT JOIN exception e ON e.service_id = s.id
JOIN contact c ON c.partner_id = c.id
where c.main = 1 or c.important = 1
group by s.id

当我尝试将其转换为 DQL

$result = $this->_em
    ->createQuery(
        'SELECT s.name, w.webId, r.email, p.name as PartnerName
         FROM AppModelDatabaseEntityService s
         JOIN AppModelDatabaseEntityWeb w WITH s.web = w.id
         JOIN AppModelDatabaseEntityRus r WITH w.rus = r.id
         JOIN AppModelDatabaseEntityPartner p WITH r.partner = p.id
         LEFT JOIN AppModelDatabaseEntityException e WITH e.service = s.id
         LEFT JOIN (SELECT
         p.id,
         group_concat(c.name) names,
         group_concat(c.tel) tels,
         group_concat(c.email) emails
         FROM
         AppModelDatabaseEntityPartner p
         LEFT JOIN AppModelDatabaseEntityContact c WITH c.partner = p.id
         where c.main = 1 or c.important = 1
         group by p.id) test
         WHERE test.id = p.id'
    )->getResult();

return new ArrayCollection($result);

我越来越:

[Semantical Error] line 0, col 452 near 'JOIN (SELECTrn ': Error: Subquery is not supported here

使用 QueryBuilder 是一样的..

在 DQL 或使用 QueryBuilder 中使用左连接和子查询有什么技巧吗?

谢谢

stack overflow Doctrine JOIN with subquery in DQL
原文答案
author avatar

接受的答案

当您可以使用连接和聚合处理它时,我认为您不需要为此使用子查询。就像在主查询中一样,加入您的 contact 实体并为您想要的结果集执行聚合。我已经更新了 group by 子句并添加了 select 子句中的所有非聚合列,这样即使启用了 MySQL 的严格模式,该查询也将有效,请参阅 MySQL Handling of GROUP BY 并且它将根据分组消除重复行标准

在 SQL 中它会像

SELECT
    s.id,
    s.name,
    e.exception,
    w.url,
    w.web_id,
    w.active,
    w.suspended,
    r.email,
    p.name as partnerName,
    p.id as partnerId,
    group_concat(c.name) names,
    group_concat(c.email) emails,
    group_concat(c.tel) tels
FROM service s
    left join exception e ON e.service_id = s.id
    join web w ON s.web_id = w.id
    join rus r ON w.rus_id = r.id
    join partner p ON r.partner_id = p.id
    join contact c ON c.partner_id = c.id 
WHERE c.main = 1 or c.important = 1
GROUP BY s.id,
        s.name,
        e.exception,
        w.url,
        w.web_id,
        w.active,
        w.suspended,
        r.email,
        p.name,
        p.id

我相信你需要添加一个扩展来默认支持 Mysql 在 doctrine 中的 GROUP_CONCAT 函数,这不包含在 doctrine orm 中。

要添加对 GROUP_CONCAT in doctrine 的支持,您可以按照链接答案中提到的步骤进行操作。注册后,您可以将原始 SQL 转换为 DQL,如下所示

SELECT  s.id,
        s.name,
        e.exception,
        w.url,
        w.web_id,
        w.active,
        w.suspended,
        r.email,
        p.id as partnerId,
        p.name as partnerName,
        GROUP_CONCAT(c.name) contactNames,
        GROUP_CONCAT(c.email) contactEmails,
        GROUP_CONCAT(c.tel) contactTels
FROM AppModelDatabaseEntityService s
    LEFT JOIN AppModelDatabaseEntityException e WITH e.service = s.id
    JOIN AppModelDatabaseEntityWeb w WITH s.web = w.id
    JOIN AppModelDatabaseEntityRus r WITH w.rus = r.id
    JOIN AppModelDatabaseEntityPartner p WITH r.partner = p.id
    JOIN AppModelDatabaseEntityContact c WITH c.partner = p.id
WHERE c.main = 1 or c.important = 1
GROUP BY s.id,
        s.name,
        e.exception,
        w.url,
        w.web_id,
        w.active,
        w.suspended,
        r.email,
        p.name,
        p.id

或者另一种方法是使用 ResultSetMapping 类学说,您可以在其中执行原始 SQL 并将结果映射到各个实体

$rsm = new ResultSetMapping;
$rsm->addEntityResult('AppModelDatabaseEntityService', 's');
$rsm->addFieldResult('s', 'id', 'id');
$rsm->addFieldResult('s', 'name', 'name');
$rsm->addJoinedEntityResult('AppModelDatabaseEntityException' , 'e', 's', 'exception');
$rsm->addFieldResult('e', 'exception', 'exception');
//.... other relations
$rsm->addJoinedEntityResult('AppModelDatabaseEntityPartner' , 'p', 'r', 'partner');
$rsm->addFieldResult('p', 'partnerName', 'name');
$rsm->addScalarResult('contactNames', 'contactNames');
// ... and so on

$sql = 'SELECT s.id, e.exception ... FROM service s JOIN ... WHERE ... GROUP BY s.id, e.exception ...';
$query = $this->_em->createNativeQuery($sql, $rsm);
$data = $query->getResult();

答案:

作者头像

正如 #3542 中的回答

DQL 是关于查询对象的。在 FROM 子句中支持子查询意味着 DQL 解析器无法再构建结果集映射(因为子查询返回的字段可能不再匹配对象)。

你最好的选择是改用 sql

$conn = $this->getEntityManager()->getConnection();
$sql = 'Your query';
$stmt = $conn->prepare($sql);
$stmt->execute(); //Bind what parameters you need