Symfony2 Doctrine query builder as a subquery in FROM clause

Question

I got a query using query builder and that is assigned to $qb variable. It works fine both from PHP and from the DB. Now, I was trying to use that query as a subquery like below:

    $subQuery = $qb->getQuery()->getSql();
    $query = 'select res.some_name
              from ('.$subQuery.') as res';

But I get the following exception:

Caused by Doctrine\DBAL\Driver\PDOException: SQLSTATE[HY000]: General error: 1 no such column: res.some_name

As Doctrine already converted the $qb to something like this where Doctrine converted the original SQL query. For instance, there was something called AS legalentity_name but it's showing AS name1:

select res.some_name from (SELECT o0_.id AS id0, o0_.name AS name1, b1_.id AS id2, b1_.display AS display3, m2_.id AS id4, m2_.total AS total5 FROM Invoice i3_ INNER JOIN CodeableItem c4_ ON i3_.id = c4_.id INNER JOIN MonetaryItem m2_ ON i3_.id = m2_.id AND (1=1) INNER JOIN LineItem l5_ ON c4_.id = l5_.codeableItem_id LEFT JOIN MonetaryItem m6_ ON l5_.id = m6_.id AND (1=1) LEFT JOIN PresetLineItem p7_ ON c4_.id = p7_.codeableItem_id LEFT JOIN MonetaryItem m8_ ON p7_.id = m8_.id AND (1=1) INNER JOIN OrgUnit o0_ ON c4_.legalentity_id = o0_.id AND (1=1) INNER JOIN monetaryitem_listitem m9_ ON m2_.id = m9_.monetaryitem_id INNER JOIN BWListItem b1_ ON b1_.id = m9_.bwlistitem_id AND (1=1) INNER JOIN BWList b10_ ON b1_.bwlist_id = b10_.id AND (1=1) WHERE b10_.type = 'Vendor' GROUP BY c4_.legalentity_id, b1_.active, b1_.attributes, b1_.display, b1_.created, b1_.updated, b1_.lft, b1_.lvl, b1_.rgt, b1_.root, b1_.id, b1_.orgunit_id, b1_.bwlist_id, b1_.parent_id, b1_.rootou_id, m2_.created, m2_.updated, m2_.subtotal, m2_.total, m2_.description, m2_.id, c4_.number, c4_.externalId, c4_.status, c4_.overriddenDuringApproval, i3_.invoiceDate, i3_.dueDate, i3_.poNumber, m2_.rootou_id, c4_.image_id, c4_.legalentity_id, c4_.creator, c4_.owner_id ORDER BY o0_.name ASC, b1_.display ASC) as res

My question is: how can I use the raw SQL from $subQuery? Any help would be really beneficial. Cheers!

Answer 1

First off, subqueries in DQL is not possible. See Selecting from subquery in DQL

Secondly, you are putting computed SQL from Doctrine Query Language (DQL) into a subquery. This does not work as the database cannot find the column due to DQL prefixing characters/numeric values to the columns.

This is so the entities can be mapped correctly when using DQL.

You will need to build the subquery NOT using the DQL language (stop using that query builder, not sure if there is one that builds raw SQL).