Join subquery with doctrine 2 DBAL

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:

Important it’s the createQueryBuilder of connection not the one of the entity manager.

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

$select = $connection->createQueryBuilder()
    ->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);

Leave a Comment