Join subquery with doctrine 2 DBAL

by

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'))
    ->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);

More Related Contents:

  1. How to do left join in Doctrine?
  2. Doctrine 2 and Many-to-many link table with an extra field
  3. How to use WHERE IN with Doctrine 2
  4. Select entries between dates in doctrine 2
  5. Subquery in doctrine2 notIN Function
  6. How to set up entity (doctrine) for database view in Symfony 2
  7. Query on a many-to-many relationship using Doctrine with Symfony2
  8. How can I use DATE() in Doctrine 2 DQL?
  9. Trying to Show 87 mysql rows in 3 html tables using PHP and mysql [closed]
  10. While loop inserts three times
  11. Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in? [duplicate]
  12. Auto Increment ID’s of 4 digit
  13. php nested while loop. Inner loop works only once [closed]
  14. Problame with files on SQL
  15. Row count with PDO
  16. Best way to INSERT many values in mysqli?
  17. How to view query error in PDO PHP
  18. How to populate second dropdown based on selection of first dropdown using jQuery/AJAX and PHP/MySQL?
  19. Resetting array pointer in PDO results
  20. Notice: Array to string conversion in
  21. How to search for slash (\) in MySQL? and why escaping (\) not required for where (=) but for Like is required?
  22. Is mysql_insert_id safe to use?
  23. PDO::PARAM for type decimal?
  24. How to execute raw queries with Laravel 5.1?
  25. Can’t find FULLTEXT index matching the column list (indexes is set)
  26. Extension mysqli is missing, phpmyadmin doesn’t work
  27. MySQL and PHP – insert NULL rather than empty string
  28. How to get MySql 8 to run with laravel? [duplicate]
  29. recursive function to get all the child categories
  30. JSON vs. Serialized Array in database [closed]

Leave a Comment