Join subquery with doctrine 2 DBAL

Question

I'm refactoring a Zend Framework 2 application to use doctrine 2.5 DBAL instead of Zend_DB (ZF1). I have the following Zend_Db query:

$subSelect = $db->select()
    ->from('user_survey_status_entries', array('userSurveyID', 'timestamp' => 'MIN(timestamp)'))
    ->where('status = ?', UserSurveyStatus::ACCESSED)
    ->group('userSurveyID');


$select = $db->select()
    // $selectColNames contains columns both from the main query and 
    // the subquery (e.g. firstAccess.timestamp AS dateFirstAccess).
    ->from(array('us' => 'user_surveys'), $selectColNames)
    ->joinLeft(array('firstAccess' => $subSelect), 'us.userSurveyID = firstAccess.userSurveyID', array())
    ->where('us.surveyID = ?', $surveyID);

This results in the following MySQL query:

SELECT `us`.`userSurveyID`, 
    // More columns from main query `us`
    `firstAccess`.`timestamp` AS `dateFirstAccess`
FROM `user_surveys` AS `us`
LEFT JOIN (
    SELECT `user_survey_status_entries`.`userSurveyID`, 
            MIN(timestamp) AS `timestamp` 
    FROM `user_survey_status_entries` 
    WHERE (status = 20) 
    GROUP BY `userSurveyID`
) AS `firstAccess` ON us.userSurveyID = firstAccess.userSurveyID 
WHERE (us.surveyID = '10')

I can't figure out how to join the subquery using the doctrine 2.5 query builder. In the main query, I need to select columns from the subquery.

I have read here that doctrine does not support joining subqueries. If that's still true, can I write this query in another way using the SQL query builder of doctrine DBAL? Native SQL may not be a good solution for me, as this query will be dynamically extended later in the code.

Retrieve the result of your subSelect then use it as parameter of your select. — Veve
@Veve the result of the subSelect will be an array with thousands of elements, I don't think it's viable to retrieve it first and use it as a parameter in the main query. — aimfeld
@Ocramius I'm trying to build SQL. The query is ultimately used for a csv data export, after adding a bunch of stuff not shown here. — aimfeld
So this is DBAL-related (please do make that clear in the question). Is the query builder strictly necessary in this process? Given that it doesn't really guarantee portability, I'd suggest phasing it out completely. I don't see any support for joining non-tables in the QB: github.com/doctrine/dbal/blob/… If your aim is portable queries via a query builder (SQL), then the ZF2 query builder may indeed be a better solution, although not all engine allow joining to subqueries. — Ocramius

aimfeld aimfeld · Accepted Answer · 2016-02-26T16:29:26

I've found a solution by adapting this DQL example to DBAL. The trick is to get the raw SQL of the subquery, wrap it in brackets, and join it. Parameters used in the subquery must be set in the main query:

$subSelect = $connection->createQueryBuilder()
    ->select(array('userSurveyID', 'MIN(timestamp) timestamp'))
    ->from('user_survey_status_entries')
    // Instead of setting the parameter in the main query below, it could be quoted here:
    // ->where('status = ' . $connection->quote(UserSurveyStatus::ACCESSED))
    ->where('status = :status')
    ->groupBy('userSurveyID');

$select = $connection->createQueryBuilder()
    ->select($selectColNames)
    ->from('user_surveys', 'us')
    // Get raw subquery SQL and wrap in brackets.
    ->leftJoin('us', sprintf('(%s)', $subSelect->getSQL()), 'firstAccess', 'us.userSurveyID = firstAccess.userSurveyID')
    // Parameter used in subquery must be set in main query.
    ->setParameter('status', UserSurveyStatus::ACCESSED)
    ->where('us.surveyID = :surveyID')->setParameter('surveyID', $surveyID);

Join subquery with doctrine 2 DBAL

2 Answers