doutuoji8418
2014-02-25 07:49
浏览 59
已采纳

在zf2和Doctrine2中编写连接查询查询

I have a query in zf2 and doctrine orm. I am joining the result of two queries on the same table by its primary_key.

It seems, I am making a syntax error and not able to find out.

$query = $this->getEntityManager()->createQuery(
    "select tc_result1.id as id ,tc_result1.displayId as displayId,tc_result1.activeFlag,tc_result1.hash
        from
        (
            SELECT *
            FROM (
                    SELECT id, display_id,active_flag,hash
                    FROM Test\Entity\TestCase tc_inner1
                    where tc_inner1.activeFlag=0 and tc_inner1.product = :productId
                    ORDER BY tc_inner1.displayId DESC
                 ) a
            GROUP BY hash 
        ) AS tc_result1

        join

        (
            SELECT *
            FROM (
                    SELECT id, displayId,activeFlag,hash
                    FROM Test\Entity\TestCase tc_inner2
                    where tc_inner2.activeFlag=0 and tc_inner2.product = :productId
                    ORDER BY tc_inner2.displayId DESC
                 ) a
            GROUP BY hash 
        ) AS tc_result2
        on
        tc_result1.id = tc_result2.id"
   );
$query->setParameter("productId", $productId);

I got following error:

Additional information: Doctrine\ORM\Query\QueryException File: /var/www/test-suite/vendor/doctrine/orm/lib/Doctrine/ORM/Query/QueryException.php:63

Message:

[Semantical Error] line 0, col 153 near '(': Error: Class '(' is not defined.

图片转代码服务由CSDN问答提供 功能建议

我在zf2和doctrine orm中有一个查询。 我正在通过其primary_key在同一个表上加入两个查询的结果。

看来,我正在制作语法错误而无法找到答案。

  $ query = $ this-> getEntityManager() - > createQuery(
“选择tc_result1.id作为id,tc_result1.displayId作为displayId,tc_result1.activeFlag, 来自
的tc_result1.hash 
(
 SELECT * 
 FROM(
 SELECT id,display_id,active_flag,hash 
 FROM Test \ Entity \ TestCase tc_inner1 
其中tc_inner1.activeFlag = 0和tc_inner1.product =:  productId 
 ORDER BY tc_inner1.displayId DESC 
)a 
 GROUP BY hash 
)AS tc_result1 
 
 join 
 
(
 SELECT * 
 FROM(
 SELECT id,displayId,activeFlag,  hash 
 FROM Test \ Entity \ TestCase tc_inner2 
其中tc_inner2.activeFlag = 0和tc_inner2.product =:productId 
 ORDER BY tc_inner2.displayId DESC 
)a 
 GROUP BY哈希
)AS tc_result2 
 on  
  tc_result1.id = tc_result2.id“
); 
 $ query-> setParameter(”productId“,$ productId); 
   
 
 

我收到了以下错误 :

其他信息:Doctrine \ ORM \ Query \ QueryException文件: / var / www / test-suite / vendor / doctrine / orm / lib / Doctrine / ORM / Query / QueryException.php:63

消息:

  [语义错误]第0行,第153行靠近'(': 错误:类'('未定义。
   
  
 
  • 写回答
  • 好问题 提建议
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • dongzhashou0116 2014-02-25 12:12
    已采纳

    The answer I gave to a similar question yesterday on the doctrine-user mailinglist is also applicable here:

    What you are doing wrong here is: defining an ON-clause. That is how you'd do it in SQL. In DQL however you define the relationship between your entities in the mapping-information (via annotations, xml, yaml or php) and in the query you use that relationship. So: define a OneToOne, OneToMany, ManyToOne or ManyToMany relationship and use that in the query (and consequently no need to use a ON).

    And related (from the same thread):

    you are not querying the tables with DQL, but the entities! The entities (objects) form a network, the object graph. You can "walk" that graph. The associations are not defined in the query (with ON or WHERE), but in the mapping. That is the whole point of an ORM; otherwise there is no need to use it and you could better just directly query the database with SQL.

    已采纳该答案
    评论
    解决 无用
    打赏 举报
  • dongqu1783 2014-02-25 13:21

    I got the problem. Doctrine queries to Entity class rather than the table. It expects an entity class after

    select tc_result1.id as id ,tc_result1.displayId as displayId,tc_result1.activeFlag,tc_result1.hash
                    from
                    (
    

    A solution to this problem is using ResultSetMappingBuilder object , using which we can write the query in native sql and map the result to doctrine object

    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题