Doctrine JOIN with subquery in DQL

Question

I have this 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

and when I tried to convert it to 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);

I'm getting:

[Semantical Error] line 0, col 452 near 'JOIN (SELECT\r\n ': Error: Subquery is not supported here

Using QueryBuilder is same..

Is there any hack to use left join with subquery either in DQL or using QueryBuilder?

Thank you

Answer 1

I don't think that you will need a subquery for this when you can handle it with join and aggregation. Like in the main query join your contact entity and perform aggregation for your desired result set. I have updated the group by clause and added all non-aggregated columns that are in the select clause so that this query will be valid even if MySQL's strict mode is enabled see MySQL Handling of GROUP BY and it will eliminate duplicate rows also as per the grouping criteria

In SQL it will be like

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

I believe you will need to add an extension to support Mysql's GROUP_CONCAT function in doctrine by default this is not included in doctrine orm.

To add support for GROUP_CONCAT in doctrine you can follow the steps mentioned in the linked answer. Once it's registered you can transform your raw SQL to DQL as

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 App\Model\Database\Entity\Service s
    LEFT JOIN App\Model\Database\Entity\Exception e WITH e.service = s.id
    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
    JOIN App\Model\Database\Entity\Contact 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
    

Or another approach will be to use ResultSetMapping class of doctrine in which you can execute your raw SQL and map the result on the respective entities

$rsm = new ResultSetMapping;
$rsm->addEntityResult('App\Model\Database\Entity\Service', 's');
$rsm->addFieldResult('s', 'id', 'id');
$rsm->addFieldResult('s', 'name', 'name');
$rsm->addJoinedEntityResult('App\Model\Database\Entity\Exception' , 'e', 's', 'exception');
$rsm->addFieldResult('e', 'exception', 'exception');
//.... other relations
$rsm->addJoinedEntityResult('App\Model\Database\Entity\Partner' , '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();

Answer 2

As answered in #3542

DQL is about querying objects. Supporting subselects in the FROM clause means that the DQL parser is not able to build the result set mapping anymore (as the fields returned by the subquery may not match the object anymore).

Your best bet would be to use sql instead

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