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.

Woodhead

Retrieve the result of your subSelect then use it as parameter of your select. – Hurlburt

@Hurlburt 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. – Woodhead

Is this DQL or SQL that you are trying to build? – Marjana

@Marjana 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. – Woodhead

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. – Marjana

@Marjana Thanks, I now clarified that this is DBAL related in the question. I have to use a query builder, as the query gets very complex (not shown above). I'll give doctrine DBAL a last shot and switch to the ZF2 query builder if unsuccessful. – Woodhead

I made a feature request for joining subqueries in DBAL, it may be considered for version 3.0: github.com/doctrine/dbal/issues/2305 – Woodhead

this solution can be very usefull: https://mcmap.net/q/670084/-subquery-with-limit-in-doctrine-duplicate – Caveator

Answer 1

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

Woodhead

I think there is an error in this line : ->leftJoin('us', sprintf('(%s)', $subSelect->getSQL()), 'firstAccess', 'us.userSurveyID = firstAccess.userSurveyID') The third argument must be Expr\Join::ON or Expr\Join::WITH. – Ortiz

Expr\Join::ON and Expr\Join::WITH only applies to the ORM namespaced QueryBuilder. e.g. \Doctrine\ORM\Query\Expr\Join for use with \Doctrine\ORM\QueryBuilder::join The standalone DBAL does not have a Join object. The DBAL joins are implicitly always an ON conditional. See: doctrine-project.org/api/dbal/2.5/… vs doctrine-project.org/api/orm/2.5/… – Mcgurn

does not works, ref to #24600939 – Ammon

does not work, you will got [Semantical Error] line 0, col 174 near 'JOIN (SELECT': Error: Subquery is not supported here – Waggle

It really works, but read carefully all auhor's recommendations includig commments inside the code – Dunnage

all column names, table names needs to be raw sql, not entity name or field names. Also note, $connection->createQueryBuilder() is used not $em->>createQueryBuilder() – Il

I was trying to solve my issue by this way, but I used EXISTS and avoided raw SQL. – Frontolysis

Answer 2

To answer this part of your question:

I can't figure out how to join the subquery using the doctrine 2.5 query builder

You can make 2 query builder instances and use the DQL from the second one inside a clause of your first query. An example:

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

Check examples here or here or find more using Google

Roundfaced

This may work for subqueries in the where clause, but I need to select columns in the main query from the subquery. – Woodhead

@Woodhead you can do that (tie to your main query) by setting a u1 and u2 and then do andWhere('u1.id = u2.id');. – Roundfaced

I have updated the description with the final mysql query. It would be very helpful if you could give a code example to achieve this query (I don't get what you mean, sorry). – Woodhead

@Woodhead Check this example where they use i and i2 or read this blog where they use m and sm and then do 'm.name = sm.name'. You should be able to figure it out with those examples... – Roundfaced

Thanks, I have studied both these examples. This technique works for using subqueries in the where function, but not in the leftJoin function. As mentioned by Ocramius here, I think doctrine provides no solution without using native SQL. I might have to use the ZF2 query builder instead... – Woodhead

He writes "This could be solved using an IN(SUBQUERY) in my opinion: link" and that is exactly what I wrote in my answer. So don't give up yet... :D – Roundfaced

That is my answer for DQL queries: we are talking about SQL+DBAL here. The answer also is based on the ORM, not the DBAL: it is incorrect (and should be deleted) – Marjana

@Marjana In his question he writes: "I can't figure out how to join the subquery using the doctrine 2.5 query builder" And so what I did was answer (as good as possible) how he can join a subquery using a query builder... I don't see what is wrong with my (upvoted) answer and why it should be deleted...? – Roundfaced

I read $qb2->getDQL() in the answer: the question was edited to clarify that this is about the DBAL, not the ORM ;-) – Marjana

I can't find ->getDQL() in the QueryBuilder of a project using symfony 3.4. There are those two lines in the composer require: "doctrine/doctrine-bundle": "^1.6" (actually locked to 1.12.13 and "doctrine/orm": "^2.5" (actually locked to 2.7.5 I guess that DBal is there because it's recursively required by the doctrine/orm. – Waldenburg

Instead, I've used the ->getSQL() and worked fine. Checked in the Dcotrine source code that the ->in() (I'm using that one instead of the notIn() adds the parentheses around the subquery, so it's all safe to use the direct SQL. I'm not using user-introduced data, so no fear of injection. – Waldenburg