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, Jan 13 '16 at 14:20
@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, Jan 13 '16 at 16:57
@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, Jan 20 '16 at 13:37
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: https://github.com/doctrine/dbal/blob/02e5b61baedbeb1832702d0feac3174e70c32c74/lib/Doctrine/DBAL/Query/QueryBuilder.php#L621-L728 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, Jan 20 '16 at 13:55
@Ocramius 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. — aimfeld, Jan 20 '16 at 14:32
I made a feature request for joining subqueries in DBAL, it may be considered for version 3.0: https://github.com/doctrine/dbal/issues/2305 — aimfeld, Feb 26 '16 at 14:55
this solution can be very usefull: https://stackoverflow.com/a/46663949/2450812 — murtho, Jan 15 '19 at 21:23

score 24 · Accepted Answer · edited Nov 18 '22 at 17:58

24

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

edited Nov 18 '22 at 17:58

Jon

677
6
13

answered Feb 26 '16 at 16:29

aimfeld

2,931
7
32
43

3

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.` – Tsounabe Nov 10 '16 at 23:25
1

`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: http://www.doctrine-project.org/api/dbal/2.5/class-Doctrine.DBAL.Query.QueryBuilder.html#_join vs http://www.doctrine-project.org/api/orm/2.5/class-Doctrine.ORM.QueryBuilder.html#_join – Will B. Feb 27 '17 at 14:10
3

does not works, ref to https://stackoverflow.com/questions/24600439/error-in-nested-subquery-in-dql-class-is-not-defined – Victor Nazarov Nov 28 '17 at 20:46
8

does not work, you will got [Semantical Error] line 0, col 174 near 'JOIN (SELECT': Error: Subquery is not supported here – ZeroCool Nov 05 '20 at 16:38
2

It really works, but read carefully all auhor's recommendations includig commments inside the code – Alex Gore Apr 19 '21 at 10:17
2

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() – vishal May 20 '21 at 10:41
I was trying to solve my issue by this way, but I used EXISTS and avoided raw SQL. – Oleg Dmitrochenko Feb 14 '23 at 08:49

score 6 · Answer 2 · edited May 23 '17 at 11:54

6

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

edited May 23 '17 at 11:54

Community

1
1

answered Jan 13 '16 at 15:45

Wilt

41,477
12
152
203

1

This may work for subqueries in the where clause, but I need to select columns in the main query from the subquery. – aimfeld Jan 13 '16 at 17:18
@aimfeld you can do that (tie to your main query) by setting a `u1` and `u2` and then do `andWhere('u1.id = u2.id');`. – Wilt Jan 13 '16 at 17:31
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). – aimfeld Jan 15 '16 at 10:59
@aimfeld Check [this example](http://stackoverflow.com/a/6638146/1697459) where they use `i` and `i2` or read [this blog](https://www.philipphoffmann.de/blog/2012/08/29/a-bulletproof-pattern-for-creating-doctrine-subqueries-of-any-complexity/) where they use `m` and `sm` and then do `'m.name = sm.name'`. You should be able to figure it out with those examples... – Wilt Jan 15 '16 at 11:05
1

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](http://stackoverflow.com/users/347063/ocramius) [here](https://groups.google.com/forum/#!topic/doctrine-user/0rNbXlD0E_8), I think doctrine provides no solution without using native SQL. I might have to use the ZF2 query builder instead... – aimfeld Jan 15 '16 at 15:06
1

He writes *"This could be solved using an IN(SUBQUERY) in my opinion: [link](http://www.google.com/url?q=http%3A%2F%2Fwww.doctrine-project.org%2Fdocs%2Form%2F2.1%2Fen%2Freference%2Fdql-doctrine-query-language.html%23dql-select-examples&sa=D&sntz=1&usg=AFQjCNHnVxd7sIdPeoGPhwyt9w9g2gqV4Q)"* and that is exactly what I wrote in my answer. So don't give up yet... :D – Wilt Jan 15 '16 at 15:38
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) – Ocramius Jan 21 '16 at 11:50
@Ocramius 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...? – Wilt Jan 21 '16 at 12:10
I read `$qb2->getDQL()` in the answer: the question was edited to clarify that this is about the DBAL, not the ORM ;-) – Ocramius Jan 22 '16 at 17:41
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`. – Xavi Montero Apr 08 '22 at 23:21
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. – Xavi Montero Apr 09 '22 at 17:09

Join subquery with doctrine 2 DBAL

2 Answers2

Linked