dtoqa66028 2015-03-24 13:55
浏览 79
已采纳

Symfony2 Doctrine查询构建器作为FROM子句中的子查询

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!

  • 写回答

1条回答 默认 最新

  • dongzhuo5425 2015-03-24 15:16
    关注

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

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
编辑
预览

报告相同问题?

悬赏问题

  • ¥15 python 3.8.0版本,安装官方库ibm_db遇到问题,提示找不到ibm_db模块。如何解决?
  • ¥15 TMUXHS4412如何防止静电,
  • ¥30 Metashape软件中如何将建模后的图像中的植被与庄稼点云删除
  • ¥20 机械振动学课后习题求解答
  • ¥15 IEC61850 客户端和服务端的通讯机制
  • ¥15 MAX98357a(关键词-播放音频)
  • ¥15 Linux误删文件,请求帮助
  • ¥15 IBMP550小型机使用串口登录操作系统
  • ¥15 关于#python#的问题:现已知七自由度机器人的DH参数,利用DH参数求解机器人的逆运动学解目前使用的PSO算法
  • ¥15 发那科机器人与设备通讯配置