Attach a subquery with doctrine 2 DBAL

Question

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

$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 (eg 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 cannot figure out how to join the subquery using the doctrine 2.5 query constructor. In the main query, I need to select the columns from the subquery.

I read here that the doctrine does not support joining subqueries. If this is still true, can I write this query differently using the DBAL doctrine SQL query builder? Native SQL may not be the best solution for me, as this query will be dynamically expanded later in the code.

Answer 1

I found a solution by adapting this

Answer 2

To answer this part of your question:

I can't figure out how to join a subquery using doctrine 2.5 query constructor

You can create two instances of the query builder and use the DQL from the second inside the sentence of the first query. Example:

 ->where($qb->expr()->notIn('u.id', $qb2->getDQL()) 

Check out the examples here or here or find more using Google