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

編集履歴

質問編集履歴

2

解決した記述方法を追記

2016/10/06 08:27

投稿

退会済みユーザー
test CHANGED
File without changes
test CHANGED
@@ -160,66 +160,6 @@
160
160
 
161
161
 
162
162
 
163
- Stack trace:
164
-
165
- #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))
166
-
167
- #1 /vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php(971): Doctrine\ORM\Query\Parser->semanticalError('Class '(' is no...', Array)
168
-
169
- #2 /vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php(1702): Doctrine\ORM\Query\Parser->AbstractSchemaName()
170
-
171
- #3 /vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php(1674): Doctrine\ORM\Query\Parser->RangeVariableDeclaration()
172
-
173
- #4 /vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php(1569): Doctrine\ORM\Query\Parser->Join()
174
-
175
- #5 /vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php(1292): Doctrine\ORM\Query\Parser->IdentificationVariableDeclaration()
176
-
177
- #6 /vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php(874): Doctrine\ORM\Query\Parser->FromClause()
178
-
179
- #7 /vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php(843): Doctrine\ORM\Query\Parser->SelectStatement()
180
-
181
- #8 /vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php(268): Doctrine\ORM\Query\Parser->QueryLanguage()
182
-
183
- #9 /vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php(351): Doctrine\ORM\Query\Parser->getAST()
184
-
185
- #10 /vendor/doctrine/orm/lib/Doctrine/ORM/Query.php(281): Doctrine\ORM\Query\Parser->parse()
186
-
187
- #11 /vendor/doctrine/orm/lib/Doctrine/ORM/Query.php(293): Doctrine\ORM\Query->_parse()
188
-
189
- #12 /vendor/doctrine/orm/lib/Doctrine/ORM/AbstractQuery.php(969): Doctrine\ORM\Query->_doExecute()
190
-
191
- #13 /vendor/doctrine/orm/lib/Doctrine/ORM/AbstractQuery.php(924): Doctrine\ORM\AbstractQuery->executeIgnoreQueryCache(NULL, 1)
192
-
193
- #14 /vendor/doctrine/orm/lib/Doctrine/ORM/AbstractQuery.php(727): Doctrine\ORM\AbstractQuery->execute(NULL, 1)
194
-
195
- #15 /module/Application/src/Application/Db/EntityTable/SalesTable.php(118): Doctrine\ORM\AbstractQuery->getResult()
196
-
197
- #16 /module/Application/src/Application/Controller/SalesController.php(48): Application\Db\EntityTable\SalesTable->getEntityByMonthly(Object(DateTime))
198
-
199
- #17 /vendor/zendframework/zend-mvc/src/Controller/AbstractActionController.php(82): Application\Controller\SalesController->indexAction()
200
-
201
- #18 [internal function]: Zend\Mvc\Controller\AbstractActionController->onDispatch(Object(Zend\Mvc\MvcEvent))
202
-
203
- #19 /vendor/zendframework/zend-eventmanager/src/EventManager.php(490): call_user_func(Array, Object(Zend\Mvc\MvcEvent))
204
-
205
- #20 /vendor/zendframework/zend-eventmanager/src/EventManager.php(263): Zend\EventManager\EventManager->triggerListeners('dispatch', Object(Zend\Mvc\MvcEvent), Object(Closure))
206
-
207
- #21 /vendor/zendframework/zend-mvc/src/Controller/AbstractController.php(118): Zend\EventManager\EventManager->triggerEventUntil(Object(Closure), Object(Zend\Mvc\MvcEvent))
208
-
209
- #22 /vendor/zendframework/zend-mvc/src/DispatchListener.php(118): Zend\Mvc\Controller\AbstractController->dispatch(Object(Zend\Http\PhpEnvironment\Request), Object(Zend\Http\PhpEnvironment\Response))
210
-
211
- #23 [internal function]: Zend\Mvc\DispatchListener->onDispatch(Object(Zend\Mvc\MvcEvent))
212
-
213
- #24 /vendor/zendframework/zend-eventmanager/src/EventManager.php(490): call_user_func(Array, Object(Zend\Mvc\MvcEvent))
214
-
215
- #25 /vendor/zendframework/zend-eventmanager/src/EventManager.php(263): Zend\EventManager\EventManager->triggerListeners('dispatch', Object(Zend\Mvc\MvcEvent), Object(Closure))
216
-
217
- #26 /vendor/zendframework/zend-mvc/src/Application.php(340): Zend\EventManager\EventManager->triggerEventUntil(Object(Closure), Object(Zend\Mvc\MvcEvent))
218
-
219
- #27 /public/index.php(50): Zend\Mvc\Application->run()
220
-
221
- #28 {main}
222
-
223
163
 
224
164
 
225
165
  Previous exceptions:
@@ -238,68 +178,44 @@
238
178
 
239
179
  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
240
180
 
241
-
242
-
243
- Stack trace:
244
-
245
- #0 /vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php(483): Doctrine\ORM\Query\QueryException::dqlError('SELECT a, b.tot...')
246
-
247
- #1 /vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php(971): Doctrine\ORM\Query\Parser->semanticalError('Class '(' is no...', Array)
248
-
249
- #2 /vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php(1702): Doctrine\ORM\Query\Parser->AbstractSchemaName()
250
-
251
- #3 /vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php(1674): Doctrine\ORM\Query\Parser->RangeVariableDeclaration()
252
-
253
- #4 /vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php(1569): Doctrine\ORM\Query\Parser->Join()
254
-
255
- #5 /vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php(1292): Doctrine\ORM\Query\Parser->IdentificationVariableDeclaration()
256
-
257
- #6 /vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php(874): Doctrine\ORM\Query\Parser->FromClause()
258
-
259
- #7 /vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php(843): Doctrine\ORM\Query\Parser->SelectStatement()
260
-
261
- #8 /vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php(268): Doctrine\ORM\Query\Parser->QueryLanguage()
262
-
263
- #9 /vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php(351): Doctrine\ORM\Query\Parser->getAST()
264
-
265
- #10 /vendor/doctrine/orm/lib/Doctrine/ORM/Query.php(281): Doctrine\ORM\Query\Parser->parse()
266
-
267
- #11 /vendor/doctrine/orm/lib/Doctrine/ORM/Query.php(293): Doctrine\ORM\Query->_parse()
268
-
269
- #12 /vendor/doctrine/orm/lib/Doctrine/ORM/AbstractQuery.php(969): Doctrine\ORM\Query->_doExecute()
270
-
271
- #13 /vendor/doctrine/orm/lib/Doctrine/ORM/AbstractQuery.php(924): Doctrine\ORM\AbstractQuery->executeIgnoreQueryCache(NULL, 1)
272
-
273
- #14 /vendor/doctrine/orm/lib/Doctrine/ORM/AbstractQuery.php(727): Doctrine\ORM\AbstractQuery->execute(NULL, 1)
274
-
275
- #15 /module/Application/src/Application/Db/EntityTable/SalesTable.php(118): Doctrine\ORM\AbstractQuery->getResult()
276
-
277
- #16 /module/Application/src/Application/Controller/SalesController.php(48): Application\Db\EntityTable\SalesTable->getEntityByMonthly(Object(DateTime))
278
-
279
- #17 /vendor/zendframework/zend-mvc/src/Controller/AbstractActionController.php(82): Application\Controller\SalesController->indexAction()
280
-
281
- #18 [internal function]: Zend\Mvc\Controller\AbstractActionController->onDispatch(Object(Zend\Mvc\MvcEvent))
282
-
283
- #19 /vendor/zendframework/zend-eventmanager/src/EventManager.php(490): call_user_func(Array, Object(Zend\Mvc\MvcEvent))
284
-
285
- #20 /vendor/zendframework/zend-eventmanager/src/EventManager.php(263): Zend\EventManager\EventManager->triggerListeners('dispatch', Object(Zend\Mvc\MvcEvent), Object(Closure))
286
-
287
- #21 /vendor/zendframework/zend-mvc/src/Controller/AbstractController.php(118): Zend\EventManager\EventManager->triggerEventUntil(Object(Closure), Object(Zend\Mvc\MvcEvent))
288
-
289
- #22 /vendor/zendframework/zend-mvc/src/DispatchListener.php(118): Zend\Mvc\Controller\AbstractController->dispatch(Object(Zend\Http\PhpEnvironment\Request), Object(Zend\Http\PhpEnvironment\Response))
290
-
291
- #23 [internal function]: Zend\Mvc\DispatchListener->onDispatch(Object(Zend\Mvc\MvcEvent))
292
-
293
- #24 /vendor/zendframework/zend-eventmanager/src/EventManager.php(490): call_user_func(Array, Object(Zend\Mvc\MvcEvent))
294
-
295
- #25 /vendor/zendframework/zend-eventmanager/src/EventManager.php(263): Zend\EventManager\EventManager->triggerListeners('dispatch', Object(Zend\Mvc\MvcEvent), Object(Closure))
296
-
297
- #26 /vendor/zendframework/zend-mvc/src/Application.php(340): Zend\EventManager\EventManager->triggerEventUntil(Object(Closure), Object(Zend\Mvc\MvcEvent))
298
-
299
- #27 /public/index.php(50): Zend\Mvc\Application->run()
300
-
301
- #28 {main}
302
-
303
-
304
-
305
- ```
181
+ ```
182
+
183
+
184
+
185
+ ###解決した記述
186
+
187
+
188
+
189
+ ```php
190
+
191
+ $qb1 = $em->createQueryBuilder();
192
+
193
+ $qb2 = clone $qb1;
194
+
195
+
196
+
197
+ $qb2->from('Application\Entity\Sales', 'b')
198
+
199
+ ->select('SUM(b.value)')
200
+
201
+ ->andWhere($qb2->expr()->lte('b.date', ':date'))
202
+
203
+ ->andWhere($qb2->expr()->eq('b.itemId', 'a.itemId'));
204
+
205
+
206
+
207
+ $qb1->from('Application\Entity\Sales', 'a')
208
+
209
+ ->select('a')
210
+
211
+ ->addSelect(sprintf('(%s) AS total', $qb2->getDQL()))
212
+
213
+ ->andWhere($qb1->expr()->eq('a.date', ':date'))
214
+
215
+ ->setParameter('date', $date);
216
+
217
+
218
+
219
+ $results = $qb1->getQuery()->getResult();
220
+
221
+ ```

1

追記

2016/10/06 08:27

投稿

退会済みユーザー
test CHANGED
File without changes
test CHANGED
@@ -124,7 +124,13 @@
124
124
 
125
125
  ```
126
126
 
127
-
127
+ $qb1, $qb2 を innerJoin する前ソースコードに setParameter を設定して結果を確認すると問題はないので Entity には問題ないです。
128
+
129
+ エラーメッセージが出力しているクエリ文は理想に近い形なのですが
130
+
131
+ innerJoin にサブクエリは使えないのでしょうか？
132
+
133
+ または他のやり方があるのでしょうか？
128
134
 
129
135
 
130
136