时间戳

首页 > 解决方案 > Doctrine JOIN 与 DQL 中的子查询

php - 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 App\Model\Database\Entity\Service s
         JOIN App\Model\Database\Entity\Web w WITH s.web = w.id
         JOIN App\Model\Database\Entity\Rus r WITH w.rus = r.id
         JOIN App\Model\Database\Entity\Partner p WITH r.partner = p.id
         LEFT JOIN App\Model\Database\Entity\Exception 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
         App\Model\Database\Entity\Partner p
         LEFT JOIN App\Model\Database\Entity\Contact 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 (SELECT\r\n ': Error: Subquery is not supported here

使用 QueryBuilder 是一样的..

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

谢谢

标签： phpdoctrine-ormdoctrine

解决方案

正如#3542中的回答

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

你最好的选择是使用 sql

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

推荐阅读