teratail
回答率
85.30%
teratail header banner
teratail header banner
質問するログイン新規登録
トップに関する質問Doctrine 2 で InnerJoin にサブクエリを用いる方法

編集履歴

質問編集履歴

2

解決した記述方法を追記

2016/10/06 08:27

投稿

退会済みユーザー
title CHANGED
File without changes
body CHANGED
@@ -79,36 +79,6 @@
79
79
  Message:
80
80
  [Semantical Error] line 0, col 69 near '(SELECT b.itemId,': Error: Class '(' is not defined.
81
81
 
82
- Stack trace:
83
- #0 /vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php(483): Doctrine\ORM\Query\QueryException::semanticalError('line 0, col 69 ...', Object(Doctrine\ORM\Query\QueryException))
84
- #1 /vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php(971): Doctrine\ORM\Query\Parser->semanticalError('Class '(' is no...', Array)
85
- #2 /vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php(1702): Doctrine\ORM\Query\Parser->AbstractSchemaName()
86
- #3 /vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php(1674): Doctrine\ORM\Query\Parser->RangeVariableDeclaration()
87
- #4 /vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php(1569): Doctrine\ORM\Query\Parser->Join()
88
- #5 /vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php(1292): Doctrine\ORM\Query\Parser->IdentificationVariableDeclaration()
89
- #6 /vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php(874): Doctrine\ORM\Query\Parser->FromClause()
90
- #7 /vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php(843): Doctrine\ORM\Query\Parser->SelectStatement()
91
- #8 /vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php(268): Doctrine\ORM\Query\Parser->QueryLanguage()
92
- #9 /vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php(351): Doctrine\ORM\Query\Parser->getAST()
93
- #10 /vendor/doctrine/orm/lib/Doctrine/ORM/Query.php(281): Doctrine\ORM\Query\Parser->parse()
94
- #11 /vendor/doctrine/orm/lib/Doctrine/ORM/Query.php(293): Doctrine\ORM\Query->_parse()
95
- #12 /vendor/doctrine/orm/lib/Doctrine/ORM/AbstractQuery.php(969): Doctrine\ORM\Query->_doExecute()
96
- #13 /vendor/doctrine/orm/lib/Doctrine/ORM/AbstractQuery.php(924): Doctrine\ORM\AbstractQuery->executeIgnoreQueryCache(NULL, 1)
97
- #14 /vendor/doctrine/orm/lib/Doctrine/ORM/AbstractQuery.php(727): Doctrine\ORM\AbstractQuery->execute(NULL, 1)
98
- #15 /module/Application/src/Application/Db/EntityTable/SalesTable.php(118): Doctrine\ORM\AbstractQuery->getResult()
99
- #16 /module/Application/src/Application/Controller/SalesController.php(48): Application\Db\EntityTable\SalesTable->getEntityByMonthly(Object(DateTime))
100
- #17 /vendor/zendframework/zend-mvc/src/Controller/AbstractActionController.php(82): Application\Controller\SalesController->indexAction()
101
- #18 [internal function]: Zend\Mvc\Controller\AbstractActionController->onDispatch(Object(Zend\Mvc\MvcEvent))
102
- #19 /vendor/zendframework/zend-eventmanager/src/EventManager.php(490): call_user_func(Array, Object(Zend\Mvc\MvcEvent))
103
- #20 /vendor/zendframework/zend-eventmanager/src/EventManager.php(263): Zend\EventManager\EventManager->triggerListeners('dispatch', Object(Zend\Mvc\MvcEvent), Object(Closure))
104
- #21 /vendor/zendframework/zend-mvc/src/Controller/AbstractController.php(118): Zend\EventManager\EventManager->triggerEventUntil(Object(Closure), Object(Zend\Mvc\MvcEvent))
105
- #22 /vendor/zendframework/zend-mvc/src/DispatchListener.php(118): Zend\Mvc\Controller\AbstractController->dispatch(Object(Zend\Http\PhpEnvironment\Request), Object(Zend\Http\PhpEnvironment\Response))
106
- #23 [internal function]: Zend\Mvc\DispatchListener->onDispatch(Object(Zend\Mvc\MvcEvent))
107
- #24 /vendor/zendframework/zend-eventmanager/src/EventManager.php(490): call_user_func(Array, Object(Zend\Mvc\MvcEvent))
108
- #25 /vendor/zendframework/zend-eventmanager/src/EventManager.php(263): Zend\EventManager\EventManager->triggerListeners('dispatch', Object(Zend\Mvc\MvcEvent), Object(Closure))
109
- #26 /vendor/zendframework/zend-mvc/src/Application.php(340): Zend\EventManager\EventManager->triggerEventUntil(Object(Closure), Object(Zend\Mvc\MvcEvent))
110
- #27 /public/index.php(50): Zend\Mvc\Application->run()
111
- #28 {main}
112
82
 
113
83
  Previous exceptions:
114
84
  Doctrine\ORM\Query\QueryException
@@ -118,36 +88,24 @@
118
88
 
119
89
  Message:
120
90
  SELECT a, b.total FROM Application\Entity\Sales a INNER JOIN (SELECT b.itemId, SUM(b.value) AS total FROM Application\Entity\Sales b WHERE b.date <= :date GROUP BY b.itemId) b WITH b.itemId = a.itemId WHERE a.date = :date
91
+ ```
121
92
 
122
- Stack trace:
123
- #0 /vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php(483): Doctrine\ORM\Query\QueryException::dqlError('SELECT a, b.tot...')
124
- #1 /vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php(971): Doctrine\ORM\Query\Parser->semanticalError('Class '(' is no...', Array)
125
- #2 /vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php(1702): Doctrine\ORM\Query\Parser->AbstractSchemaName()
126
- #3 /vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php(1674): Doctrine\ORM\Query\Parser->RangeVariableDeclaration()
127
- #4 /vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php(1569): Doctrine\ORM\Query\Parser->Join()
128
- #5 /vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php(1292): Doctrine\ORM\Query\Parser->IdentificationVariableDeclaration()
129
- #6 /vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php(874): Doctrine\ORM\Query\Parser->FromClause()
130
- #7 /vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php(843): Doctrine\ORM\Query\Parser->SelectStatement()
131
- #8 /vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php(268): Doctrine\ORM\Query\Parser->QueryLanguage()
132
- #9 /vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php(351): Doctrine\ORM\Query\Parser->getAST()
133
- #10 /vendor/doctrine/orm/lib/Doctrine/ORM/Query.php(281): Doctrine\ORM\Query\Parser->parse()
134
- #11 /vendor/doctrine/orm/lib/Doctrine/ORM/Query.php(293): Doctrine\ORM\Query->_parse()
135
- #12 /vendor/doctrine/orm/lib/Doctrine/ORM/AbstractQuery.php(969): Doctrine\ORM\Query->_doExecute()
136
- #13 /vendor/doctrine/orm/lib/Doctrine/ORM/AbstractQuery.php(924): Doctrine\ORM\AbstractQuery->executeIgnoreQueryCache(NULL, 1)
137
- #14 /vendor/doctrine/orm/lib/Doctrine/ORM/AbstractQuery.php(727): Doctrine\ORM\AbstractQuery->execute(NULL, 1)
138
- #15 /module/Application/src/Application/Db/EntityTable/SalesTable.php(118): Doctrine\ORM\AbstractQuery->getResult()
139
- #16 /module/Application/src/Application/Controller/SalesController.php(48): Application\Db\EntityTable\SalesTable->getEntityByMonthly(Object(DateTime))
140
- #17 /vendor/zendframework/zend-mvc/src/Controller/AbstractActionController.php(82): Application\Controller\SalesController->indexAction()
141
- #18 [internal function]: Zend\Mvc\Controller\AbstractActionController->onDispatch(Object(Zend\Mvc\MvcEvent))
142
- #19 /vendor/zendframework/zend-eventmanager/src/EventManager.php(490): call_user_func(Array, Object(Zend\Mvc\MvcEvent))
143
- #20 /vendor/zendframework/zend-eventmanager/src/EventManager.php(263): Zend\EventManager\EventManager->triggerListeners('dispatch', Object(Zend\Mvc\MvcEvent), Object(Closure))
144
- #21 /vendor/zendframework/zend-mvc/src/Controller/AbstractController.php(118): Zend\EventManager\EventManager->triggerEventUntil(Object(Closure), Object(Zend\Mvc\MvcEvent))
145
- #22 /vendor/zendframework/zend-mvc/src/DispatchListener.php(118): Zend\Mvc\Controller\AbstractController->dispatch(Object(Zend\Http\PhpEnvironment\Request), Object(Zend\Http\PhpEnvironment\Response))
146
- #23 [internal function]: Zend\Mvc\DispatchListener->onDispatch(Object(Zend\Mvc\MvcEvent))
147
- #24 /vendor/zendframework/zend-eventmanager/src/EventManager.php(490): call_user_func(Array, Object(Zend\Mvc\MvcEvent))
148
- #25 /vendor/zendframework/zend-eventmanager/src/EventManager.php(263): Zend\EventManager\EventManager->triggerListeners('dispatch', Object(Zend\Mvc\MvcEvent), Object(Closure))
149
- #26 /vendor/zendframework/zend-mvc/src/Application.php(340): Zend\EventManager\EventManager->triggerEventUntil(Object(Closure), Object(Zend\Mvc\MvcEvent))
150
- #27 /public/index.php(50): Zend\Mvc\Application->run()
151
- #28 {main}
93
+ ###解決した記述
152
94
 
95
+ ```php
96
+ $qb1 = $em->createQueryBuilder();
97
+ $qb2 = clone $qb1;
98
+
99
+ $qb2->from('Application\Entity\Sales', 'b')
100
+ ->select('SUM(b.value)')
101
+ ->andWhere($qb2->expr()->lte('b.date', ':date'))
102
+ ->andWhere($qb2->expr()->eq('b.itemId', 'a.itemId'));
103
+
104
+ $qb1->from('Application\Entity\Sales', 'a')
105
+ ->select('a')
106
+ ->addSelect(sprintf('(%s) AS total', $qb2->getDQL()))
107
+ ->andWhere($qb1->expr()->eq('a.date', ':date'))
108
+ ->setParameter('date', $date);
109
+
110
+ $results = $qb1->getQuery()->getResult();
153
111
  ```

1

追記

2016/10/06 08:27

投稿

退会済みユーザー
title CHANGED
File without changes
body CHANGED
@@ -61,9 +61,12 @@
61
61
 
62
62
  $results = $qb1->getQuery()->getResult();
63
63
  ```
64
+ $qb1, $qb2 を innerJoin する前ソースコードに setParameter を設定して結果を確認すると問題はないので Entity には問題ないです。
65
+ エラーメッセージが出力しているクエリ文は理想に近い形なのですが
66
+ innerJoin にサブクエリは使えないのでしょうか？
67
+ または他のやり方があるのでしょうか？
64
68
 
65
69
 
66
-
67
70
  ###発生している問題・エラーメッセージ
68
71
 
69
72
  ```