1
|
|
|
<?php |
2
|
|
|
|
3
|
|
|
namespace Doctrine\Tests\ORM\Query; |
4
|
|
|
|
5
|
|
|
use Doctrine\DBAL\Types\Type as DBALType; |
6
|
|
|
use Doctrine\ORM\Query; |
7
|
|
|
|
8
|
|
|
class SelectSqlGenerationTest extends \Doctrine\Tests\OrmTestCase |
9
|
|
|
{ |
10
|
|
|
private $_em; |
11
|
|
|
|
12
|
|
|
protected function setUp() |
13
|
|
|
{ |
14
|
|
|
$this->_em = $this->_getTestEntityManager(); |
15
|
|
|
} |
16
|
|
|
|
17
|
|
|
/** |
18
|
|
|
* Assert a valid SQL generation. |
19
|
|
|
* |
20
|
|
|
* @param string $dqlToBeTested |
21
|
|
|
* @param string $sqlToBeConfirmed |
22
|
|
|
* @param array $queryHints |
23
|
|
|
* @param array $queryParams |
24
|
|
|
*/ |
25
|
|
|
public function assertSqlGeneration($dqlToBeTested, $sqlToBeConfirmed, array $queryHints = array(), array $queryParams = array()) |
26
|
|
|
{ |
27
|
|
|
try { |
28
|
|
|
$query = $this->_em->createQuery($dqlToBeTested); |
29
|
|
|
|
30
|
|
|
foreach ($queryParams AS $name => $value) { |
31
|
|
|
$query->setParameter($name, $value); |
32
|
|
|
} |
33
|
|
|
|
34
|
|
|
$query->setHint(Query::HINT_FORCE_PARTIAL_LOAD, true) |
35
|
|
|
->useQueryCache(false); |
36
|
|
|
|
37
|
|
|
foreach ($queryHints AS $name => $value) { |
38
|
|
|
$query->setHint($name, $value); |
39
|
|
|
} |
40
|
|
|
|
41
|
|
|
$sqlGenerated = $query->getSQL(); |
42
|
|
|
|
43
|
|
|
parent::assertEquals( |
|
|
|
|
44
|
|
|
$sqlToBeConfirmed, |
45
|
|
|
$sqlGenerated, |
46
|
|
|
sprintf('"%s" is not equal of "%s"', $sqlGenerated, $sqlToBeConfirmed) |
47
|
|
|
); |
48
|
|
|
|
49
|
|
|
$query->free(); |
50
|
|
|
} catch (\Exception $e) { |
51
|
|
|
$this->fail($e->getMessage() ."\n".$e->getTraceAsString()); |
52
|
|
|
} |
53
|
|
|
} |
54
|
|
|
|
55
|
|
|
/** |
56
|
|
|
* Asser an invalid SQL generation. |
57
|
|
|
* |
58
|
|
|
* @param string $dqlToBeTested |
59
|
|
|
* @param string $expectedException |
60
|
|
|
* @param array $queryHints |
61
|
|
|
* @param array $queryParams |
62
|
|
|
*/ |
63
|
|
|
public function assertInvalidSqlGeneration($dqlToBeTested, $expectedException, array $queryHints = array(), array $queryParams = array()) |
64
|
|
|
{ |
65
|
|
|
$this->setExpectedException($expectedException); |
66
|
|
|
|
67
|
|
|
$query = $this->_em->createQuery($dqlToBeTested); |
68
|
|
|
|
69
|
|
|
foreach ($queryParams AS $name => $value) { |
70
|
|
|
$query->setParameter($name, $value); |
71
|
|
|
} |
72
|
|
|
|
73
|
|
|
$query->setHint(Query::HINT_FORCE_PARTIAL_LOAD, true) |
74
|
|
|
->useQueryCache(false); |
75
|
|
|
|
76
|
|
|
foreach ($queryHints AS $name => $value) { |
77
|
|
|
$query->setHint($name, $value); |
78
|
|
|
} |
79
|
|
|
|
80
|
|
|
$sql = $query->getSql(); |
81
|
|
|
$query->free(); |
82
|
|
|
|
83
|
|
|
// If we reached here, test failed |
84
|
|
|
$this->fail($sql); |
|
|
|
|
85
|
|
|
} |
86
|
|
|
|
87
|
|
|
/** |
88
|
|
|
* @group DDC-3697 |
89
|
|
|
*/ |
90
|
|
|
public function testJoinWithRangeVariablePutsConditionIntoSqlWhereClause() |
91
|
|
|
{ |
92
|
|
|
$this->assertSqlGeneration( |
93
|
|
|
'SELECT c.id FROM Doctrine\Tests\Models\Company\CompanyPerson c JOIN Doctrine\Tests\Models\Company\CompanyPerson r WHERE c.spouse = r AND r.id = 42', |
94
|
|
|
'SELECT c0_.id AS id_0 FROM company_persons c0_ INNER JOIN company_persons c1_ WHERE c0_.spouse_id = c1_.id AND c1_.id = 42', |
95
|
|
|
array(Query::HINT_FORCE_PARTIAL_LOAD => true) |
96
|
|
|
); |
97
|
|
|
} |
98
|
|
|
|
99
|
|
|
/** |
100
|
|
|
* @group DDC-3697 |
101
|
|
|
*/ |
102
|
|
|
public function testJoinWithRangeVariableAndInheritancePutsConditionIntoSqlWhereClause() |
103
|
|
|
{ |
104
|
|
|
/* |
105
|
|
|
* Basically like the previous test, but this time load data for the inherited objects as well. |
106
|
|
|
* The important thing is that the ON clauses in LEFT JOINs only contain the conditions necessary to join the appropriate inheritance table |
107
|
|
|
* whereas the filtering condition must remain in the SQL WHERE clause. |
108
|
|
|
*/ |
109
|
|
|
$this->assertSqlGeneration( |
110
|
|
|
'SELECT c.id FROM Doctrine\Tests\Models\Company\CompanyPerson c JOIN Doctrine\Tests\Models\Company\CompanyPerson r WHERE c.spouse = r AND r.id = 42', |
111
|
|
|
'SELECT c0_.id AS id_0 FROM company_persons c0_ LEFT JOIN company_managers c1_ ON c0_.id = c1_.id LEFT JOIN company_employees c2_ ON c0_.id = c2_.id INNER JOIN company_persons c3_ LEFT JOIN company_managers c4_ ON c3_.id = c4_.id LEFT JOIN company_employees c5_ ON c3_.id = c5_.id WHERE c0_.spouse_id = c3_.id AND c3_.id = 42', |
112
|
|
|
array(Query::HINT_FORCE_PARTIAL_LOAD => false) |
113
|
|
|
); |
114
|
|
|
} |
115
|
|
|
|
116
|
|
|
public function testSupportsSelectForAllFields() |
117
|
|
|
{ |
118
|
|
|
$this->assertSqlGeneration( |
119
|
|
|
'SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u', |
120
|
|
|
'SELECT c0_.id AS id_0, c0_.status AS status_1, c0_.username AS username_2, c0_.name AS name_3 FROM cms_users c0_' |
121
|
|
|
); |
122
|
|
|
} |
123
|
|
|
|
124
|
|
|
public function testSupportsSelectForOneField() |
125
|
|
|
{ |
126
|
|
|
$this->assertSqlGeneration( |
127
|
|
|
'SELECT u.id FROM Doctrine\Tests\Models\CMS\CmsUser u', |
128
|
|
|
'SELECT c0_.id AS id_0 FROM cms_users c0_' |
129
|
|
|
); |
130
|
|
|
} |
131
|
|
|
|
132
|
|
|
public function testSupportsSelectForOneNestedField() |
133
|
|
|
{ |
134
|
|
|
$this->assertSqlGeneration( |
135
|
|
|
'SELECT u.id FROM Doctrine\Tests\Models\CMS\CmsArticle a JOIN a.user u', |
136
|
|
|
'SELECT c0_.id AS id_0 FROM cms_articles c1_ INNER JOIN cms_users c0_ ON c1_.user_id = c0_.id' |
137
|
|
|
); |
138
|
|
|
} |
139
|
|
|
|
140
|
|
|
public function testSupportsSelectForAllNestedField() |
141
|
|
|
{ |
142
|
|
|
$this->assertSqlGeneration( |
143
|
|
|
'SELECT a FROM Doctrine\Tests\Models\CMS\CmsArticle a JOIN a.user u ORDER BY u.name ASC', |
144
|
|
|
'SELECT c0_.id AS id_0, c0_.topic AS topic_1, c0_.text AS text_2, c0_.version AS version_3 FROM cms_articles c0_ INNER JOIN cms_users c1_ ON c0_.user_id = c1_.id ORDER BY c1_.name ASC' |
145
|
|
|
); |
146
|
|
|
} |
147
|
|
|
|
148
|
|
|
public function testNotExistsExpression() |
149
|
|
|
{ |
150
|
|
|
$this->assertSqlGeneration( |
151
|
|
|
'SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE NOT EXISTS (SELECT p.phonenumber FROM Doctrine\Tests\Models\CMS\CmsPhonenumber p WHERE p.phonenumber = 1234)', |
152
|
|
|
'SELECT c0_.id AS id_0, c0_.status AS status_1, c0_.username AS username_2, c0_.name AS name_3 FROM cms_users c0_ WHERE NOT EXISTS (SELECT c1_.phonenumber FROM cms_phonenumbers c1_ WHERE c1_.phonenumber = 1234)' |
153
|
|
|
); |
154
|
|
|
} |
155
|
|
|
|
156
|
|
|
public function testSupportsSelectForMultipleColumnsOfASingleComponent() |
157
|
|
|
{ |
158
|
|
|
$this->assertSqlGeneration( |
159
|
|
|
'SELECT u.username, u.name FROM Doctrine\Tests\Models\CMS\CmsUser u', |
160
|
|
|
'SELECT c0_.username AS username_0, c0_.name AS name_1 FROM cms_users c0_' |
161
|
|
|
); |
162
|
|
|
} |
163
|
|
|
|
164
|
|
|
public function testSupportsSelectUsingMultipleFromComponents() |
165
|
|
|
{ |
166
|
|
|
$this->assertSqlGeneration( |
167
|
|
|
'SELECT u, p FROM Doctrine\Tests\Models\CMS\CmsUser u, Doctrine\Tests\Models\CMS\CmsPhonenumber p WHERE u = p.user', |
168
|
|
|
'SELECT c0_.id AS id_0, c0_.status AS status_1, c0_.username AS username_2, c0_.name AS name_3, c1_.phonenumber AS phonenumber_4 FROM cms_users c0_, cms_phonenumbers c1_ WHERE c0_.id = c1_.user_id' |
169
|
|
|
); |
170
|
|
|
} |
171
|
|
|
|
172
|
|
|
public function testSupportsJoinOnMultipleComponents() |
173
|
|
|
{ |
174
|
|
|
$this->assertSqlGeneration( |
175
|
|
|
'SELECT u, p FROM Doctrine\Tests\Models\CMS\CmsUser u JOIN Doctrine\Tests\Models\CMS\CmsPhonenumber p WITH u = p.user', |
176
|
|
|
'SELECT c0_.id AS id_0, c0_.status AS status_1, c0_.username AS username_2, c0_.name AS name_3, c1_.phonenumber AS phonenumber_4 FROM cms_users c0_ INNER JOIN cms_phonenumbers c1_ ON (c0_.id = c1_.user_id)' |
177
|
|
|
); |
178
|
|
|
} |
179
|
|
|
|
180
|
|
|
public function testSupportsJoinOnMultipleComponentsWithJoinedInheritanceType() |
181
|
|
|
{ |
182
|
|
|
$this->assertSqlGeneration( |
183
|
|
|
'SELECT e FROM Doctrine\Tests\Models\Company\CompanyEmployee e JOIN Doctrine\Tests\Models\Company\CompanyManager m WITH e.id = m.id', |
184
|
|
|
'SELECT c0_.id AS id_0, c0_.name AS name_1, c1_.salary AS salary_2, c1_.department AS department_3, c1_.startDate AS startDate_4, c0_.discr AS discr_5 FROM company_employees c1_ INNER JOIN company_persons c0_ ON c1_.id = c0_.id INNER JOIN company_managers c2_ INNER JOIN company_employees c4_ ON c2_.id = c4_.id INNER JOIN company_persons c3_ ON c2_.id = c3_.id AND (c0_.id = c3_.id)' |
185
|
|
|
); |
186
|
|
|
|
187
|
|
|
$this->assertSqlGeneration( |
188
|
|
|
'SELECT e FROM Doctrine\Tests\Models\Company\CompanyEmployee e LEFT JOIN Doctrine\Tests\Models\Company\CompanyManager m WITH e.id = m.id', |
189
|
|
|
'SELECT c0_.id AS id_0, c0_.name AS name_1, c1_.salary AS salary_2, c1_.department AS department_3, c1_.startDate AS startDate_4, c0_.discr AS discr_5 FROM company_employees c1_ INNER JOIN company_persons c0_ ON c1_.id = c0_.id LEFT JOIN company_managers c2_ INNER JOIN company_employees c4_ ON c2_.id = c4_.id INNER JOIN company_persons c3_ ON c2_.id = c3_.id ON (c0_.id = c3_.id)' |
190
|
|
|
); |
191
|
|
|
} |
192
|
|
|
|
193
|
|
|
public function testSupportsSelectWithCollectionAssociationJoin() |
194
|
|
|
{ |
195
|
|
|
$this->assertSqlGeneration( |
196
|
|
|
'SELECT u, p FROM Doctrine\Tests\Models\CMS\CmsUser u JOIN u.phonenumbers p', |
197
|
|
|
'SELECT c0_.id AS id_0, c0_.status AS status_1, c0_.username AS username_2, c0_.name AS name_3, c1_.phonenumber AS phonenumber_4 FROM cms_users c0_ INNER JOIN cms_phonenumbers c1_ ON c0_.id = c1_.user_id' |
198
|
|
|
); |
199
|
|
|
} |
200
|
|
|
|
201
|
|
|
public function testSupportsSelectWithSingleValuedAssociationJoin() |
202
|
|
|
{ |
203
|
|
|
$this->assertSqlGeneration( |
204
|
|
|
'SELECT u, a FROM Doctrine\Tests\Models\Forum\ForumUser u JOIN u.avatar a', |
205
|
|
|
'SELECT f0_.id AS id_0, f0_.username AS username_1, f1_.id AS id_2 FROM forum_users f0_ INNER JOIN forum_avatars f1_ ON f0_.avatar_id = f1_.id' |
206
|
|
|
); |
207
|
|
|
} |
208
|
|
|
|
209
|
|
|
public function testSelectCorrelatedSubqueryComplexMathematicalExpression() |
210
|
|
|
{ |
211
|
|
|
$this->assertSqlGeneration( |
212
|
|
|
'SELECT (SELECT (count(p.phonenumber)+5)*10 FROM Doctrine\Tests\Models\CMS\CmsPhonenumber p JOIN p.user ui WHERE ui.id = u.id) AS c FROM Doctrine\Tests\Models\CMS\CmsUser u', |
213
|
|
|
'SELECT (SELECT (count(c0_.phonenumber) + 5) * 10 AS sclr_1 FROM cms_phonenumbers c0_ INNER JOIN cms_users c1_ ON c0_.user_id = c1_.id WHERE c1_.id = c2_.id) AS sclr_0 FROM cms_users c2_' |
214
|
|
|
); |
215
|
|
|
} |
216
|
|
|
|
217
|
|
|
public function testSelectComplexMathematicalExpression() |
218
|
|
|
{ |
219
|
|
|
$this->assertSqlGeneration( |
220
|
|
|
'SELECT (count(p.phonenumber)+5)*10 FROM Doctrine\Tests\Models\CMS\CmsPhonenumber p JOIN p.user ui WHERE ui.id = ?1', |
221
|
|
|
'SELECT (count(c0_.phonenumber) + 5) * 10 AS sclr_0 FROM cms_phonenumbers c0_ INNER JOIN cms_users c1_ ON c0_.user_id = c1_.id WHERE c1_.id = ?' |
222
|
|
|
); |
223
|
|
|
} |
224
|
|
|
|
225
|
|
|
/* NOT (YET?) SUPPORTED. |
226
|
|
|
Can be supported if SimpleSelectExpression supports SingleValuedPathExpression instead of StateFieldPathExpression. |
227
|
|
|
|
228
|
|
|
public function testSingleAssociationPathExpressionInSubselect() |
229
|
|
|
{ |
230
|
|
|
$this->assertSqlGeneration( |
231
|
|
|
'SELECT (SELECT p.user FROM Doctrine\Tests\Models\CMS\CmsPhonenumber p WHERE p.user = u) user_id FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE u.id = ?1', |
232
|
|
|
'SELECT (SELECT c0_.user_id FROM cms_phonenumbers c0_ WHERE c0_.user_id = c1_.id) AS sclr_0 FROM cms_users c1_ WHERE c1_.id = ?' |
233
|
|
|
); |
234
|
|
|
}*/ |
235
|
|
|
|
236
|
|
|
/** |
237
|
|
|
* @group DDC-1077 |
238
|
|
|
*/ |
239
|
|
|
public function testConstantValueInSelect() |
240
|
|
|
{ |
241
|
|
|
$this->assertSqlGeneration( |
242
|
|
|
"SELECT u.name, 'foo' AS bar FROM Doctrine\Tests\Models\CMS\CmsUser u", |
243
|
|
|
"SELECT c0_.name AS name_0, 'foo' AS sclr_1 FROM cms_users c0_" |
244
|
|
|
); |
245
|
|
|
} |
246
|
|
|
|
247
|
|
|
public function testSupportsOrderByWithAscAsDefault() |
248
|
|
|
{ |
249
|
|
|
$this->assertSqlGeneration( |
250
|
|
|
'SELECT u FROM Doctrine\Tests\Models\Forum\ForumUser u ORDER BY u.id', |
251
|
|
|
'SELECT f0_.id AS id_0, f0_.username AS username_1 FROM forum_users f0_ ORDER BY f0_.id ASC' |
252
|
|
|
); |
253
|
|
|
} |
254
|
|
|
|
255
|
|
|
public function testSupportsOrderByAsc() |
256
|
|
|
{ |
257
|
|
|
$this->assertSqlGeneration( |
258
|
|
|
'SELECT u FROM Doctrine\Tests\Models\Forum\ForumUser u ORDER BY u.id asc', |
259
|
|
|
'SELECT f0_.id AS id_0, f0_.username AS username_1 FROM forum_users f0_ ORDER BY f0_.id ASC' |
260
|
|
|
); |
261
|
|
|
} |
262
|
|
|
public function testSupportsOrderByDesc() |
263
|
|
|
{ |
264
|
|
|
$this->assertSqlGeneration( |
265
|
|
|
'SELECT u FROM Doctrine\Tests\Models\Forum\ForumUser u ORDER BY u.id desc', |
266
|
|
|
'SELECT f0_.id AS id_0, f0_.username AS username_1 FROM forum_users f0_ ORDER BY f0_.id DESC' |
267
|
|
|
); |
268
|
|
|
} |
269
|
|
|
|
270
|
|
|
public function testSupportsSelectDistinct() |
271
|
|
|
{ |
272
|
|
|
$this->assertSqlGeneration( |
273
|
|
|
'SELECT DISTINCT u.name FROM Doctrine\Tests\Models\CMS\CmsUser u', |
274
|
|
|
'SELECT DISTINCT c0_.name AS name_0 FROM cms_users c0_' |
275
|
|
|
); |
276
|
|
|
} |
277
|
|
|
|
278
|
|
|
public function testSupportsAggregateFunctionInSelectedFields() |
279
|
|
|
{ |
280
|
|
|
$this->assertSqlGeneration( |
281
|
|
|
'SELECT COUNT(u.id) FROM Doctrine\Tests\Models\CMS\CmsUser u GROUP BY u.id', |
282
|
|
|
'SELECT COUNT(c0_.id) AS sclr_0 FROM cms_users c0_ GROUP BY c0_.id' |
283
|
|
|
); |
284
|
|
|
} |
285
|
|
|
|
286
|
|
|
public function testSupportsAggregateFunctionWithSimpleArithmetic() |
287
|
|
|
{ |
288
|
|
|
$this->assertSqlGeneration( |
289
|
|
|
'SELECT MAX(u.id + 4) * 2 FROM Doctrine\Tests\Models\CMS\CmsUser u', |
290
|
|
|
'SELECT MAX(c0_.id + 4) * 2 AS sclr_0 FROM cms_users c0_' |
291
|
|
|
); |
292
|
|
|
} |
293
|
|
|
|
294
|
|
|
/** |
295
|
|
|
* @group DDC-3276 |
296
|
|
|
*/ |
297
|
|
|
public function testSupportsAggregateCountFunctionWithSimpleArithmetic() |
298
|
|
|
{ |
299
|
|
|
$connMock = $this->_em->getConnection(); |
300
|
|
|
$orgPlatform = $connMock->getDatabasePlatform(); |
301
|
|
|
|
302
|
|
|
$connMock->setDatabasePlatform(new \Doctrine\DBAL\Platforms\MySqlPlatform); |
303
|
|
|
|
304
|
|
|
$this->assertSqlGeneration( |
305
|
|
|
'SELECT COUNT(CONCAT(u.id, u.name)) FROM Doctrine\Tests\Models\CMS\CmsUser u GROUP BY u.id', |
306
|
|
|
'SELECT COUNT(CONCAT(c0_.id, c0_.name)) AS sclr_0 FROM cms_users c0_ GROUP BY c0_.id' |
307
|
|
|
); |
308
|
|
|
|
309
|
|
|
$connMock->setDatabasePlatform($orgPlatform); |
310
|
|
|
} |
311
|
|
|
|
312
|
|
|
public function testSupportsWhereClauseWithPositionalParameter() |
313
|
|
|
{ |
314
|
|
|
$this->assertSqlGeneration( |
315
|
|
|
'select u from Doctrine\Tests\Models\Forum\ForumUser u where u.id = ?1', |
316
|
|
|
'SELECT f0_.id AS id_0, f0_.username AS username_1 FROM forum_users f0_ WHERE f0_.id = ?' |
317
|
|
|
); |
318
|
|
|
} |
319
|
|
|
|
320
|
|
|
public function testSupportsWhereClauseWithNamedParameter() |
321
|
|
|
{ |
322
|
|
|
$this->assertSqlGeneration( |
323
|
|
|
'select u from Doctrine\Tests\Models\Forum\ForumUser u where u.username = :name', |
324
|
|
|
'SELECT f0_.id AS id_0, f0_.username AS username_1 FROM forum_users f0_ WHERE f0_.username = ?' |
325
|
|
|
); |
326
|
|
|
} |
327
|
|
|
|
328
|
|
|
public function testSupportsWhereAndClauseWithNamedParameters() |
329
|
|
|
{ |
330
|
|
|
$this->assertSqlGeneration( |
331
|
|
|
'select u from Doctrine\Tests\Models\Forum\ForumUser u where u.username = :name and u.username = :name2', |
332
|
|
|
'SELECT f0_.id AS id_0, f0_.username AS username_1 FROM forum_users f0_ WHERE f0_.username = ? AND f0_.username = ?' |
333
|
|
|
); |
334
|
|
|
} |
335
|
|
|
|
336
|
|
|
public function testSupportsCombinedWhereClauseWithNamedParameter() |
337
|
|
|
{ |
338
|
|
|
$this->assertSqlGeneration( |
339
|
|
|
'select u from Doctrine\Tests\Models\Forum\ForumUser u where (u.username = :name OR u.username = :name2) AND u.id = :id', |
340
|
|
|
'SELECT f0_.id AS id_0, f0_.username AS username_1 FROM forum_users f0_ WHERE (f0_.username = ? OR f0_.username = ?) AND f0_.id = ?' |
341
|
|
|
); |
342
|
|
|
} |
343
|
|
|
|
344
|
|
|
public function testSupportsAggregateFunctionInASelectDistinct() |
345
|
|
|
{ |
346
|
|
|
$this->assertSqlGeneration( |
347
|
|
|
'SELECT COUNT(DISTINCT u.name) FROM Doctrine\Tests\Models\CMS\CmsUser u', |
348
|
|
|
'SELECT COUNT(DISTINCT c0_.name) AS sclr_0 FROM cms_users c0_' |
349
|
|
|
); |
350
|
|
|
} |
351
|
|
|
|
352
|
|
|
// Ticket #668 |
353
|
|
|
public function testSupportsASqlKeywordInAStringLiteralParam() |
354
|
|
|
{ |
355
|
|
|
$this->assertSqlGeneration( |
356
|
|
|
"SELECT u.name FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE u.name LIKE '%foo OR bar%'", |
357
|
|
|
"SELECT c0_.name AS name_0 FROM cms_users c0_ WHERE c0_.name LIKE '%foo OR bar%'" |
358
|
|
|
); |
359
|
|
|
} |
360
|
|
|
|
361
|
|
|
public function testSupportsArithmeticExpressionsInWherePart() |
362
|
|
|
{ |
363
|
|
|
$this->assertSqlGeneration( |
364
|
|
|
'SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE ((u.id + 5000) * u.id + 3) < 10000000', |
365
|
|
|
'SELECT c0_.id AS id_0, c0_.status AS status_1, c0_.username AS username_2, c0_.name AS name_3 FROM cms_users c0_ WHERE ((c0_.id + 5000) * c0_.id + 3) < 10000000' |
366
|
|
|
); |
367
|
|
|
} |
368
|
|
|
|
369
|
|
|
public function testSupportsMultipleEntitiesInFromClause() |
370
|
|
|
{ |
371
|
|
|
$this->assertSqlGeneration( |
372
|
|
|
'SELECT u, a FROM Doctrine\Tests\Models\CMS\CmsUser u, Doctrine\Tests\Models\CMS\CmsArticle a JOIN a.user u2 WHERE u.id = u2.id', |
373
|
|
|
'SELECT c0_.id AS id_0, c0_.status AS status_1, c0_.username AS username_2, c0_.name AS name_3, c1_.id AS id_4, c1_.topic AS topic_5, c1_.text AS text_6, c1_.version AS version_7 FROM cms_users c0_, cms_articles c1_ INNER JOIN cms_users c2_ ON c1_.user_id = c2_.id WHERE c0_.id = c2_.id' |
374
|
|
|
); |
375
|
|
|
} |
376
|
|
|
|
377
|
|
|
public function testSupportsMultipleEntitiesInFromClauseUsingPathExpression() |
378
|
|
|
{ |
379
|
|
|
$this->assertSqlGeneration( |
380
|
|
|
'SELECT u, a FROM Doctrine\Tests\Models\CMS\CmsUser u, Doctrine\Tests\Models\CMS\CmsArticle a WHERE u.id = a.user', |
381
|
|
|
'SELECT c0_.id AS id_0, c0_.status AS status_1, c0_.username AS username_2, c0_.name AS name_3, c1_.id AS id_4, c1_.topic AS topic_5, c1_.text AS text_6, c1_.version AS version_7 FROM cms_users c0_, cms_articles c1_ WHERE c0_.id = c1_.user_id' |
382
|
|
|
); |
383
|
|
|
} |
384
|
|
|
|
385
|
|
|
public function testSupportsPlainJoinWithoutClause() |
386
|
|
|
{ |
387
|
|
|
$this->assertSqlGeneration( |
388
|
|
|
'SELECT u.id, a.id from Doctrine\Tests\Models\CMS\CmsUser u LEFT JOIN u.articles a', |
389
|
|
|
'SELECT c0_.id AS id_0, c1_.id AS id_1 FROM cms_users c0_ LEFT JOIN cms_articles c1_ ON c0_.id = c1_.user_id' |
390
|
|
|
); |
391
|
|
|
$this->assertSqlGeneration( |
392
|
|
|
'SELECT u.id, a.id from Doctrine\Tests\Models\CMS\CmsUser u JOIN u.articles a', |
393
|
|
|
'SELECT c0_.id AS id_0, c1_.id AS id_1 FROM cms_users c0_ INNER JOIN cms_articles c1_ ON c0_.id = c1_.user_id' |
394
|
|
|
); |
395
|
|
|
} |
396
|
|
|
|
397
|
|
|
/** |
398
|
|
|
* @group DDC-135 |
399
|
|
|
*/ |
400
|
|
|
public function testSupportsJoinAndWithClauseRestriction() |
401
|
|
|
{ |
402
|
|
|
$this->assertSqlGeneration( |
403
|
|
|
"SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u LEFT JOIN u.articles a WITH a.topic LIKE '%foo%'", |
404
|
|
|
"SELECT c0_.id AS id_0, c0_.status AS status_1, c0_.username AS username_2, c0_.name AS name_3 FROM cms_users c0_ LEFT JOIN cms_articles c1_ ON c0_.id = c1_.user_id AND (c1_.topic LIKE '%foo%')" |
405
|
|
|
); |
406
|
|
|
$this->assertSqlGeneration( |
407
|
|
|
"SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u INNER JOIN u.articles a WITH a.topic LIKE '%foo%'", |
408
|
|
|
"SELECT c0_.id AS id_0, c0_.status AS status_1, c0_.username AS username_2, c0_.name AS name_3 FROM cms_users c0_ INNER JOIN cms_articles c1_ ON c0_.id = c1_.user_id AND (c1_.topic LIKE '%foo%')" |
409
|
|
|
); |
410
|
|
|
} |
411
|
|
|
|
412
|
|
|
/** |
413
|
|
|
* @group DDC-135 |
414
|
|
|
* @group DDC-177 |
415
|
|
|
*/ |
416
|
|
|
public function testJoinOnClause_NotYetSupported_ThrowsException() |
417
|
|
|
{ |
418
|
|
|
$this->setExpectedException('Doctrine\ORM\Query\QueryException'); |
419
|
|
|
|
420
|
|
|
$sql = $this->_em->createQuery( |
|
|
|
|
421
|
|
|
"SELECT u, a FROM Doctrine\Tests\Models\CMS\CmsUser u LEFT JOIN u.articles a ON a.topic LIKE '%foo%'" |
422
|
|
|
)->getSql(); |
423
|
|
|
} |
424
|
|
|
|
425
|
|
|
public function testSupportsMultipleJoins() |
426
|
|
|
{ |
427
|
|
|
$this->assertSqlGeneration( |
428
|
|
|
'SELECT u.id, a.id, p.phonenumber, c.id from Doctrine\Tests\Models\CMS\CmsUser u JOIN u.articles a JOIN u.phonenumbers p JOIN a.comments c', |
429
|
|
|
'SELECT c0_.id AS id_0, c1_.id AS id_1, c2_.phonenumber AS phonenumber_2, c3_.id AS id_3 FROM cms_users c0_ INNER JOIN cms_articles c1_ ON c0_.id = c1_.user_id INNER JOIN cms_phonenumbers c2_ ON c0_.id = c2_.user_id INNER JOIN cms_comments c3_ ON c1_.id = c3_.article_id' |
430
|
|
|
); |
431
|
|
|
} |
432
|
|
|
|
433
|
|
|
public function testSupportsTrimFunction() |
434
|
|
|
{ |
435
|
|
|
$this->assertSqlGeneration( |
436
|
|
|
"SELECT u.name FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE TRIM(TRAILING ' ' FROM u.name) = 'someone'", |
437
|
|
|
"SELECT c0_.name AS name_0 FROM cms_users c0_ WHERE TRIM(TRAILING ' ' FROM c0_.name) = 'someone'" |
438
|
|
|
); |
439
|
|
|
} |
440
|
|
|
|
441
|
|
|
/** |
442
|
|
|
* @group DDC-2668 |
443
|
|
|
*/ |
444
|
|
|
public function testSupportsTrimLeadingZeroString() |
445
|
|
|
{ |
446
|
|
|
$this->assertSqlGeneration( |
447
|
|
|
"SELECT u.name FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE TRIM(TRAILING '0' FROM u.name) != ''", |
448
|
|
|
"SELECT c0_.name AS name_0 FROM cms_users c0_ WHERE TRIM(TRAILING '0' FROM c0_.name) <> ''" |
449
|
|
|
); |
450
|
|
|
} |
451
|
|
|
|
452
|
|
|
// Ticket 894 |
453
|
|
|
public function testSupportsBetweenClauseWithPositionalParameters() |
454
|
|
|
{ |
455
|
|
|
$this->assertSqlGeneration( |
456
|
|
|
"SELECT u.name FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE u.id BETWEEN ?1 AND ?2", |
457
|
|
|
"SELECT c0_.name AS name_0 FROM cms_users c0_ WHERE c0_.id BETWEEN ? AND ?" |
458
|
|
|
); |
459
|
|
|
} |
460
|
|
|
|
461
|
|
|
/** |
462
|
|
|
* @group DDC-1802 |
463
|
|
|
*/ |
464
|
|
|
public function testSupportsNotBetweenForSizeFunction() |
465
|
|
|
{ |
466
|
|
|
$this->assertSqlGeneration( |
467
|
|
|
"SELECT m.name FROM Doctrine\Tests\Models\StockExchange\Market m WHERE SIZE(m.stocks) NOT BETWEEN ?1 AND ?2", |
468
|
|
|
"SELECT e0_.name AS name_0 FROM exchange_markets e0_ WHERE (SELECT COUNT(*) FROM exchange_stocks e1_ WHERE e1_.market_id = e0_.id) NOT BETWEEN ? AND ?" |
469
|
|
|
); |
470
|
|
|
} |
471
|
|
|
|
472
|
|
|
public function testSupportsFunctionalExpressionsInWherePart() |
473
|
|
|
{ |
474
|
|
|
$this->assertSqlGeneration( |
475
|
|
|
"SELECT u.name FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE TRIM(u.name) = 'someone'", |
476
|
|
|
// String quoting in the SQL usually depends on the database platform. |
477
|
|
|
// This test works with a mock connection which uses ' for string quoting. |
478
|
|
|
"SELECT c0_.name AS name_0 FROM cms_users c0_ WHERE TRIM(c0_.name) = 'someone'" |
479
|
|
|
); |
480
|
|
|
} |
481
|
|
|
|
482
|
|
|
public function testSupportsInstanceOfExpressionsInWherePart() |
483
|
|
|
{ |
484
|
|
|
$this->assertSqlGeneration( |
485
|
|
|
"SELECT u FROM Doctrine\Tests\Models\Company\CompanyPerson u WHERE u INSTANCE OF Doctrine\Tests\Models\Company\CompanyEmployee", |
486
|
|
|
"SELECT c0_.id AS id_0, c0_.name AS name_1, c0_.discr AS discr_2 FROM company_persons c0_ WHERE c0_.discr IN ('employee')" |
487
|
|
|
); |
488
|
|
|
} |
489
|
|
|
|
490
|
|
|
public function testSupportsInstanceOfExpressionInWherePartWithMultipleValues() |
491
|
|
|
{ |
492
|
|
|
// This also uses FQCNs starting with or without a backslash in the INSTANCE OF parameter |
493
|
|
|
$this->assertSqlGeneration( |
494
|
|
|
"SELECT u FROM Doctrine\Tests\Models\Company\CompanyPerson u WHERE u INSTANCE OF (Doctrine\Tests\Models\Company\CompanyEmployee, \Doctrine\Tests\Models\Company\CompanyManager)", |
495
|
|
|
"SELECT c0_.id AS id_0, c0_.name AS name_1, c0_.discr AS discr_2 FROM company_persons c0_ WHERE c0_.discr IN ('employee', 'manager')" |
496
|
|
|
); |
497
|
|
|
} |
498
|
|
|
|
499
|
|
|
/** |
500
|
|
|
* @group DDC-1194 |
501
|
|
|
*/ |
502
|
|
|
public function testSupportsInstanceOfExpressionsInWherePartPrefixedSlash() |
503
|
|
|
{ |
504
|
|
|
$this->assertSqlGeneration( |
505
|
|
|
"SELECT u FROM Doctrine\Tests\Models\Company\CompanyPerson u WHERE u INSTANCE OF \Doctrine\Tests\Models\Company\CompanyEmployee", |
506
|
|
|
"SELECT c0_.id AS id_0, c0_.name AS name_1, c0_.discr AS discr_2 FROM company_persons c0_ WHERE c0_.discr IN ('employee')" |
507
|
|
|
); |
508
|
|
|
} |
509
|
|
|
|
510
|
|
|
/** |
511
|
|
|
* @group DDC-1194 |
512
|
|
|
*/ |
513
|
|
|
public function testSupportsInstanceOfExpressionsInWherePartWithUnrelatedClass() |
514
|
|
|
{ |
515
|
|
|
$this->assertInvalidSqlGeneration( |
516
|
|
|
"SELECT u FROM Doctrine\Tests\Models\Company\CompanyPerson u WHERE u INSTANCE OF \Doctrine\Tests\Models\CMS\CmsUser", |
517
|
|
|
"Doctrine\ORM\Query\QueryException" |
518
|
|
|
); |
519
|
|
|
} |
520
|
|
|
|
521
|
|
|
public function testSupportsInstanceOfExpressionsInWherePartInDeeperLevel() |
522
|
|
|
{ |
523
|
|
|
$this->assertSqlGeneration( |
524
|
|
|
"SELECT u FROM Doctrine\Tests\Models\Company\CompanyEmployee u WHERE u INSTANCE OF Doctrine\Tests\Models\Company\CompanyManager", |
525
|
|
|
"SELECT c0_.id AS id_0, c0_.name AS name_1, c1_.salary AS salary_2, c1_.department AS department_3, c1_.startDate AS startDate_4, c0_.discr AS discr_5 FROM company_employees c1_ INNER JOIN company_persons c0_ ON c1_.id = c0_.id WHERE c0_.discr IN ('manager')" |
526
|
|
|
); |
527
|
|
|
} |
528
|
|
|
|
529
|
|
|
public function testSupportsInstanceOfExpressionsInWherePartInDeepestLevel() |
530
|
|
|
{ |
531
|
|
|
$this->assertSqlGeneration( |
532
|
|
|
"SELECT u FROM Doctrine\Tests\Models\Company\CompanyManager u WHERE u INSTANCE OF Doctrine\Tests\Models\Company\CompanyManager", |
533
|
|
|
"SELECT c0_.id AS id_0, c0_.name AS name_1, c1_.salary AS salary_2, c1_.department AS department_3, c1_.startDate AS startDate_4, c2_.title AS title_5, c0_.discr AS discr_6 FROM company_managers c2_ INNER JOIN company_employees c1_ ON c2_.id = c1_.id INNER JOIN company_persons c0_ ON c2_.id = c0_.id WHERE c0_.discr IN ('manager')" |
534
|
|
|
); |
535
|
|
|
} |
536
|
|
|
|
537
|
|
|
public function testSupportsInstanceOfExpressionsUsingInputParameterInWherePart() |
538
|
|
|
{ |
539
|
|
|
$this->assertSqlGeneration( |
540
|
|
|
"SELECT u FROM Doctrine\Tests\Models\Company\CompanyPerson u WHERE u INSTANCE OF ?1", |
541
|
|
|
"SELECT c0_.id AS id_0, c0_.name AS name_1, c0_.discr AS discr_2 FROM company_persons c0_ WHERE c0_.discr IN (?)", |
542
|
|
|
array(), array(1 => $this->_em->getClassMetadata('Doctrine\Tests\Models\Company\CompanyEmployee')) |
543
|
|
|
); |
544
|
|
|
} |
545
|
|
|
|
546
|
|
|
// Ticket #973 |
547
|
|
|
public function testSupportsSingleValuedInExpressionWithoutSpacesInWherePart() |
548
|
|
|
{ |
549
|
|
|
$this->assertSqlGeneration( |
550
|
|
|
"SELECT u.name FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE IDENTITY(u.email) IN(46)", |
551
|
|
|
"SELECT c0_.name AS name_0 FROM cms_users c0_ WHERE c0_.email_id IN (46)" |
552
|
|
|
); |
553
|
|
|
} |
554
|
|
|
|
555
|
|
|
public function testSupportsMultipleValuedInExpressionInWherePart() |
556
|
|
|
{ |
557
|
|
|
$this->assertSqlGeneration( |
558
|
|
|
'SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE u.id IN (1, 2)', |
559
|
|
|
'SELECT c0_.id AS id_0, c0_.status AS status_1, c0_.username AS username_2, c0_.name AS name_3 FROM cms_users c0_ WHERE c0_.id IN (1, 2)' |
560
|
|
|
); |
561
|
|
|
} |
562
|
|
|
|
563
|
|
|
public function testSupportsNotInExpressionInWherePart() |
564
|
|
|
{ |
565
|
|
|
$this->assertSqlGeneration( |
566
|
|
|
'SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE :id NOT IN (1)', |
567
|
|
|
'SELECT c0_.id AS id_0, c0_.status AS status_1, c0_.username AS username_2, c0_.name AS name_3 FROM cms_users c0_ WHERE ? NOT IN (1)' |
568
|
|
|
); |
569
|
|
|
} |
570
|
|
|
|
571
|
|
|
/** |
572
|
|
|
* @group DDC-1802 |
573
|
|
|
*/ |
574
|
|
|
public function testSupportsNotInExpressionForModFunction() |
575
|
|
|
{ |
576
|
|
|
$this->assertSqlGeneration( |
577
|
|
|
"SELECT u.name FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE MOD(u.id, 5) NOT IN(1,3,4)", |
578
|
|
|
"SELECT c0_.name AS name_0 FROM cms_users c0_ WHERE MOD(c0_.id, 5) NOT IN (1, 3, 4)" |
579
|
|
|
); |
580
|
|
|
} |
581
|
|
|
|
582
|
|
|
public function testInExpressionWithSingleValuedAssociationPathExpressionInWherePart() |
583
|
|
|
{ |
584
|
|
|
$this->assertSqlGeneration( |
585
|
|
|
'SELECT u FROM Doctrine\Tests\Models\Forum\ForumUser u WHERE u.avatar IN (?1, ?2)', |
586
|
|
|
'SELECT f0_.id AS id_0, f0_.username AS username_1 FROM forum_users f0_ WHERE f0_.avatar_id IN (?, ?)' |
587
|
|
|
); |
588
|
|
|
} |
589
|
|
|
|
590
|
|
|
public function testInvalidInExpressionWithSingleValuedAssociationPathExpressionOnInverseSide() |
591
|
|
|
{ |
592
|
|
|
// We do not support SingleValuedAssociationPathExpression on inverse side |
593
|
|
|
$this->assertInvalidSqlGeneration( |
594
|
|
|
"SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE u.address IN (?1, ?2)", |
595
|
|
|
"Doctrine\ORM\Query\QueryException" |
596
|
|
|
); |
597
|
|
|
} |
598
|
|
|
|
599
|
|
|
public function testSupportsConcatFunctionForMysqlAndPostgresql() |
600
|
|
|
{ |
601
|
|
|
$connMock = $this->_em->getConnection(); |
602
|
|
|
$orgPlatform = $connMock->getDatabasePlatform(); |
603
|
|
|
|
604
|
|
|
$connMock->setDatabasePlatform(new \Doctrine\DBAL\Platforms\MySqlPlatform); |
605
|
|
|
$this->assertSqlGeneration( |
606
|
|
|
"SELECT u.id FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE CONCAT(u.name, 's') = ?1", |
607
|
|
|
"SELECT c0_.id AS id_0 FROM cms_users c0_ WHERE CONCAT(c0_.name, 's') = ?" |
608
|
|
|
); |
609
|
|
|
$this->assertSqlGeneration( |
610
|
|
|
"SELECT CONCAT(u.id, u.name) FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE u.id = ?1", |
611
|
|
|
"SELECT CONCAT(c0_.id, c0_.name) AS sclr_0 FROM cms_users c0_ WHERE c0_.id = ?" |
612
|
|
|
); |
613
|
|
|
|
614
|
|
|
$connMock->setDatabasePlatform(new \Doctrine\DBAL\Platforms\PostgreSqlPlatform); |
615
|
|
|
$this->assertSqlGeneration( |
616
|
|
|
"SELECT u.id FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE CONCAT(u.name, 's') = ?1", |
617
|
|
|
"SELECT c0_.id AS id_0 FROM cms_users c0_ WHERE c0_.name || 's' = ?" |
618
|
|
|
); |
619
|
|
|
$this->assertSqlGeneration( |
620
|
|
|
"SELECT CONCAT(u.id, u.name) FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE u.id = ?1", |
621
|
|
|
"SELECT c0_.id || c0_.name AS sclr_0 FROM cms_users c0_ WHERE c0_.id = ?" |
622
|
|
|
); |
623
|
|
|
|
624
|
|
|
$connMock->setDatabasePlatform($orgPlatform); |
625
|
|
|
} |
626
|
|
|
|
627
|
|
|
public function testSupportsExistsExpressionInWherePartWithCorrelatedSubquery() |
628
|
|
|
{ |
629
|
|
|
$this->assertSqlGeneration( |
630
|
|
|
'SELECT u.id FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE EXISTS (SELECT p.phonenumber FROM Doctrine\Tests\Models\CMS\CmsPhonenumber p WHERE p.phonenumber = u.id)', |
631
|
|
|
'SELECT c0_.id AS id_0 FROM cms_users c0_ WHERE EXISTS (SELECT c1_.phonenumber FROM cms_phonenumbers c1_ WHERE c1_.phonenumber = c0_.id)' |
632
|
|
|
); |
633
|
|
|
} |
634
|
|
|
|
635
|
|
|
/** |
636
|
|
|
* @group DDC-593 |
637
|
|
|
*/ |
638
|
|
|
public function testSubqueriesInComparisonExpression() |
639
|
|
|
{ |
640
|
|
|
$this->assertSqlGeneration( |
641
|
|
|
'SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE (u.id >= (SELECT u2.id FROM Doctrine\Tests\Models\CMS\CmsUser u2 WHERE u2.name = :name)) AND (u.id <= (SELECT u3.id FROM Doctrine\Tests\Models\CMS\CmsUser u3 WHERE u3.name = :name))', |
642
|
|
|
'SELECT c0_.id AS id_0, c0_.status AS status_1, c0_.username AS username_2, c0_.name AS name_3 FROM cms_users c0_ WHERE (c0_.id >= (SELECT c1_.id FROM cms_users c1_ WHERE c1_.name = ?)) AND (c0_.id <= (SELECT c2_.id FROM cms_users c2_ WHERE c2_.name = ?))' |
643
|
|
|
); |
644
|
|
|
} |
645
|
|
|
|
646
|
|
|
public function testSupportsMemberOfExpressionOneToMany() |
647
|
|
|
{ |
648
|
|
|
// "Get all users who have $phone as a phonenumber." (*cough* doesnt really make sense...) |
649
|
|
|
$q = $this->_em->createQuery('SELECT u.id FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE :param MEMBER OF u.phonenumbers'); |
650
|
|
|
$q->setHint(Query::HINT_FORCE_PARTIAL_LOAD, true); |
651
|
|
|
|
652
|
|
|
$phone = new \Doctrine\Tests\Models\CMS\CmsPhonenumber; |
653
|
|
|
$phone->phonenumber = 101; |
654
|
|
|
$q->setParameter('param', $phone); |
655
|
|
|
|
656
|
|
|
$this->assertEquals( |
657
|
|
|
'SELECT c0_.id AS id_0 FROM cms_users c0_ WHERE EXISTS (SELECT 1 FROM cms_phonenumbers c1_ WHERE c0_.id = c1_.user_id AND c1_.phonenumber = ?)', |
658
|
|
|
$q->getSql() |
659
|
|
|
); |
660
|
|
|
} |
661
|
|
|
|
662
|
|
|
public function testSupportsMemberOfExpressionManyToMany() |
663
|
|
|
{ |
664
|
|
|
// "Get all users who are members of $group." |
665
|
|
|
$q = $this->_em->createQuery('SELECT u.id FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE :param MEMBER OF u.groups'); |
666
|
|
|
$q->setHint(Query::HINT_FORCE_PARTIAL_LOAD, true); |
667
|
|
|
|
668
|
|
|
$group = new \Doctrine\Tests\Models\CMS\CmsGroup; |
669
|
|
|
$group->id = 101; |
670
|
|
|
$q->setParameter('param', $group); |
671
|
|
|
|
672
|
|
|
$this->assertEquals( |
673
|
|
|
'SELECT c0_.id AS id_0 FROM cms_users c0_ WHERE EXISTS (SELECT 1 FROM cms_users_groups c1_ INNER JOIN cms_groups c2_ ON c1_.group_id = c2_.id WHERE c1_.user_id = c0_.id AND c2_.id IN (?))', |
674
|
|
|
$q->getSql() |
675
|
|
|
); |
676
|
|
|
} |
677
|
|
|
|
678
|
|
|
public function testSupportsMemberOfExpressionManyToManyParameterArray() |
679
|
|
|
{ |
680
|
|
|
$q = $this->_em->createQuery('SELECT u.id FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE :param MEMBER OF u.groups'); |
681
|
|
|
$q->setHint(Query::HINT_FORCE_PARTIAL_LOAD, true); |
682
|
|
|
|
683
|
|
|
$group = new \Doctrine\Tests\Models\CMS\CmsGroup; |
684
|
|
|
$group->id = 101; |
685
|
|
|
$group2 = new \Doctrine\Tests\Models\CMS\CmsGroup; |
686
|
|
|
$group2->id = 105; |
687
|
|
|
$q->setParameter('param', array($group, $group2)); |
688
|
|
|
|
689
|
|
|
$this->assertEquals( |
690
|
|
|
'SELECT c0_.id AS id_0 FROM cms_users c0_ WHERE EXISTS (SELECT 1 FROM cms_users_groups c1_ INNER JOIN cms_groups c2_ ON c1_.group_id = c2_.id WHERE c1_.user_id = c0_.id AND c2_.id IN (?))', |
691
|
|
|
$q->getSql() |
692
|
|
|
); |
693
|
|
|
} |
694
|
|
|
|
695
|
|
|
public function testSupportsMemberOfExpressionSelfReferencing() |
696
|
|
|
{ |
697
|
|
|
// "Get all persons who have $person as a friend." |
698
|
|
|
// Tough one: Many-many self-referencing ("friends") with class table inheritance |
699
|
|
|
$q = $this->_em->createQuery('SELECT p FROM Doctrine\Tests\Models\Company\CompanyPerson p WHERE :param MEMBER OF p.friends'); |
700
|
|
|
$person = new \Doctrine\Tests\Models\Company\CompanyPerson; |
701
|
|
|
$this->_em->getClassMetadata(get_class($person))->setIdentifierValues($person, array('id' => 101)); |
702
|
|
|
$q->setParameter('param', $person); |
703
|
|
|
$this->assertEquals( |
704
|
|
|
'SELECT c0_.id AS id_0, c0_.name AS name_1, c1_.title AS title_2, c2_.salary AS salary_3, c2_.department AS department_4, c2_.startDate AS startDate_5, c0_.discr AS discr_6, c0_.spouse_id AS spouse_id_7, c1_.car_id AS car_id_8 FROM company_persons c0_ LEFT JOIN company_managers c1_ ON c0_.id = c1_.id LEFT JOIN company_employees c2_ ON c0_.id = c2_.id WHERE EXISTS (SELECT 1 FROM company_persons_friends c3_ INNER JOIN company_persons c4_ ON c3_.friend_id = c4_.id WHERE c3_.person_id = c0_.id AND c4_.id IN (?))', |
705
|
|
|
$q->getSql() |
706
|
|
|
); |
707
|
|
|
} |
708
|
|
|
|
709
|
|
|
public function testSupportsMemberOfWithSingleValuedAssociation() |
710
|
|
|
{ |
711
|
|
|
// Impossible example, but it illustrates the purpose |
712
|
|
|
$q = $this->_em->createQuery('SELECT u.id FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE u.email MEMBER OF u.groups'); |
713
|
|
|
|
714
|
|
|
$this->assertEquals( |
715
|
|
|
'SELECT c0_.id AS id_0 FROM cms_users c0_ WHERE EXISTS (SELECT 1 FROM cms_users_groups c1_ INNER JOIN cms_groups c2_ ON c1_.group_id = c2_.id WHERE c1_.user_id = c0_.id AND c2_.id IN (c0_.email_id))', |
716
|
|
|
$q->getSql() |
717
|
|
|
); |
718
|
|
|
} |
719
|
|
|
|
720
|
|
|
public function testSupportsMemberOfWithIdentificationVariable() |
721
|
|
|
{ |
722
|
|
|
// Impossible example, but it illustrates the purpose |
723
|
|
|
$q = $this->_em->createQuery('SELECT u.id FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE u MEMBER OF u.groups'); |
724
|
|
|
|
725
|
|
|
$this->assertEquals( |
726
|
|
|
'SELECT c0_.id AS id_0 FROM cms_users c0_ WHERE EXISTS (SELECT 1 FROM cms_users_groups c1_ INNER JOIN cms_groups c2_ ON c1_.group_id = c2_.id WHERE c1_.user_id = c0_.id AND c2_.id IN (c0_.id))', |
727
|
|
|
$q->getSql() |
728
|
|
|
); |
729
|
|
|
} |
730
|
|
|
|
731
|
|
|
public function testSupportsCurrentDateFunction() |
732
|
|
|
{ |
733
|
|
|
$q = $this->_em->createQuery('SELECT d.id FROM Doctrine\Tests\Models\Generic\DateTimeModel d WHERE d.datetime > current_date()'); |
734
|
|
|
$q->setHint(Query::HINT_FORCE_PARTIAL_LOAD, true); |
735
|
|
|
$this->assertEquals('SELECT d0_.id AS id_0 FROM date_time_model d0_ WHERE d0_.col_datetime > CURRENT_DATE', $q->getSql()); |
736
|
|
|
} |
737
|
|
|
|
738
|
|
|
public function testSupportsCurrentTimeFunction() |
739
|
|
|
{ |
740
|
|
|
$q = $this->_em->createQuery('SELECT d.id FROM Doctrine\Tests\Models\Generic\DateTimeModel d WHERE d.time > current_time()'); |
741
|
|
|
$q->setHint(Query::HINT_FORCE_PARTIAL_LOAD, true); |
742
|
|
|
$this->assertEquals('SELECT d0_.id AS id_0 FROM date_time_model d0_ WHERE d0_.col_time > CURRENT_TIME', $q->getSql()); |
743
|
|
|
} |
744
|
|
|
|
745
|
|
|
public function testSupportsCurrentTimestampFunction() |
746
|
|
|
{ |
747
|
|
|
$q = $this->_em->createQuery('SELECT d.id FROM Doctrine\Tests\Models\Generic\DateTimeModel d WHERE d.datetime > current_timestamp()'); |
748
|
|
|
$q->setHint(Query::HINT_FORCE_PARTIAL_LOAD, true); |
749
|
|
|
$this->assertEquals('SELECT d0_.id AS id_0 FROM date_time_model d0_ WHERE d0_.col_datetime > CURRENT_TIMESTAMP', $q->getSql()); |
750
|
|
|
} |
751
|
|
|
|
752
|
|
|
public function testExistsExpressionInWhereCorrelatedSubqueryAssocCondition() |
753
|
|
|
{ |
754
|
|
|
$this->assertSqlGeneration( |
755
|
|
|
// DQL |
756
|
|
|
// The result of this query consists of all employees whose spouses are also employees. |
757
|
|
|
'SELECT DISTINCT emp FROM Doctrine\Tests\Models\CMS\CmsEmployee emp |
758
|
|
|
WHERE EXISTS ( |
759
|
|
|
SELECT spouseEmp |
760
|
|
|
FROM Doctrine\Tests\Models\CMS\CmsEmployee spouseEmp |
761
|
|
|
WHERE spouseEmp = emp.spouse)', |
762
|
|
|
// SQL |
763
|
|
|
'SELECT DISTINCT c0_.id AS id_0, c0_.name AS name_1 FROM cms_employees c0_' |
764
|
|
|
. ' WHERE EXISTS (' |
765
|
|
|
. 'SELECT c1_.id FROM cms_employees c1_ WHERE c1_.id = c0_.spouse_id' |
766
|
|
|
. ')' |
767
|
|
|
); |
768
|
|
|
} |
769
|
|
|
|
770
|
|
|
public function testExistsExpressionWithSimpleSelectReturningScalar() |
771
|
|
|
{ |
772
|
|
|
$this->assertSqlGeneration( |
773
|
|
|
// DQL |
774
|
|
|
// The result of this query consists of all employees whose spouses are also employees. |
775
|
|
|
'SELECT DISTINCT emp FROM Doctrine\Tests\Models\CMS\CmsEmployee emp |
776
|
|
|
WHERE EXISTS ( |
777
|
|
|
SELECT 1 |
778
|
|
|
FROM Doctrine\Tests\Models\CMS\CmsEmployee spouseEmp |
779
|
|
|
WHERE spouseEmp = emp.spouse)', |
780
|
|
|
// SQL |
781
|
|
|
'SELECT DISTINCT c0_.id AS id_0, c0_.name AS name_1 FROM cms_employees c0_' |
782
|
|
|
. ' WHERE EXISTS (' |
783
|
|
|
. 'SELECT 1 AS sclr_2 FROM cms_employees c1_ WHERE c1_.id = c0_.spouse_id' |
784
|
|
|
. ')' |
785
|
|
|
); |
786
|
|
|
} |
787
|
|
|
|
788
|
|
|
public function testLimitFromQueryClass() |
789
|
|
|
{ |
790
|
|
|
$q = $this->_em |
791
|
|
|
->createQuery('SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u') |
792
|
|
|
->setMaxResults(10); |
793
|
|
|
|
794
|
|
|
$this->assertEquals('SELECT c0_.id AS id_0, c0_.status AS status_1, c0_.username AS username_2, c0_.name AS name_3, c0_.email_id AS email_id_4 FROM cms_users c0_ LIMIT 10', $q->getSql()); |
795
|
|
|
} |
796
|
|
|
|
797
|
|
|
public function testLimitAndOffsetFromQueryClass() |
798
|
|
|
{ |
799
|
|
|
$q = $this->_em |
800
|
|
|
->createQuery('SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u') |
801
|
|
|
->setMaxResults(10) |
802
|
|
|
->setFirstResult(0); |
803
|
|
|
|
804
|
|
|
$this->assertEquals('SELECT c0_.id AS id_0, c0_.status AS status_1, c0_.username AS username_2, c0_.name AS name_3, c0_.email_id AS email_id_4 FROM cms_users c0_ LIMIT 10 OFFSET 0', $q->getSql()); |
805
|
|
|
} |
806
|
|
|
|
807
|
|
|
public function testSizeFunction() |
808
|
|
|
{ |
809
|
|
|
$this->assertSqlGeneration( |
810
|
|
|
"SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE SIZE(u.phonenumbers) > 1", |
811
|
|
|
"SELECT c0_.id AS id_0, c0_.status AS status_1, c0_.username AS username_2, c0_.name AS name_3 FROM cms_users c0_ WHERE (SELECT COUNT(*) FROM cms_phonenumbers c1_ WHERE c1_.user_id = c0_.id) > 1" |
812
|
|
|
); |
813
|
|
|
} |
814
|
|
|
|
815
|
|
|
public function testSizeFunctionSupportsManyToMany() |
816
|
|
|
{ |
817
|
|
|
$this->assertSqlGeneration( |
818
|
|
|
"SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE SIZE(u.groups) > 1", |
819
|
|
|
"SELECT c0_.id AS id_0, c0_.status AS status_1, c0_.username AS username_2, c0_.name AS name_3 FROM cms_users c0_ WHERE (SELECT COUNT(*) FROM cms_users_groups c1_ WHERE c1_.user_id = c0_.id) > 1" |
820
|
|
|
); |
821
|
|
|
} |
822
|
|
|
|
823
|
|
|
public function testEmptyCollectionComparisonExpression() |
824
|
|
|
{ |
825
|
|
|
$this->assertSqlGeneration( |
826
|
|
|
"SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE u.phonenumbers IS EMPTY", |
827
|
|
|
"SELECT c0_.id AS id_0, c0_.status AS status_1, c0_.username AS username_2, c0_.name AS name_3 FROM cms_users c0_ WHERE (SELECT COUNT(*) FROM cms_phonenumbers c1_ WHERE c1_.user_id = c0_.id) = 0" |
828
|
|
|
); |
829
|
|
|
$this->assertSqlGeneration( |
830
|
|
|
"SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE u.phonenumbers IS NOT EMPTY", |
831
|
|
|
"SELECT c0_.id AS id_0, c0_.status AS status_1, c0_.username AS username_2, c0_.name AS name_3 FROM cms_users c0_ WHERE (SELECT COUNT(*) FROM cms_phonenumbers c1_ WHERE c1_.user_id = c0_.id) > 0" |
832
|
|
|
); |
833
|
|
|
} |
834
|
|
|
|
835
|
|
|
public function testNestedExpressions() |
836
|
|
|
{ |
837
|
|
|
$this->assertSqlGeneration( |
838
|
|
|
"select u from Doctrine\Tests\Models\CMS\CmsUser u where u.id > 10 and u.id < 42 and ((u.id * 2) > 5)", |
839
|
|
|
"SELECT c0_.id AS id_0, c0_.status AS status_1, c0_.username AS username_2, c0_.name AS name_3 FROM cms_users c0_ WHERE c0_.id > 10 AND c0_.id < 42 AND ((c0_.id * 2) > 5)" |
840
|
|
|
); |
841
|
|
|
} |
842
|
|
|
|
843
|
|
|
public function testNestedExpressions2() |
844
|
|
|
{ |
845
|
|
|
$this->assertSqlGeneration( |
846
|
|
|
"select u from Doctrine\Tests\Models\CMS\CmsUser u where (u.id > 10) and (u.id < 42 and ((u.id * 2) > 5)) or u.id <> 42", |
847
|
|
|
"SELECT c0_.id AS id_0, c0_.status AS status_1, c0_.username AS username_2, c0_.name AS name_3 FROM cms_users c0_ WHERE (c0_.id > 10) AND (c0_.id < 42 AND ((c0_.id * 2) > 5)) OR c0_.id <> 42" |
848
|
|
|
); |
849
|
|
|
} |
850
|
|
|
|
851
|
|
|
public function testNestedExpressions3() |
852
|
|
|
{ |
853
|
|
|
$this->assertSqlGeneration( |
854
|
|
|
"select u from Doctrine\Tests\Models\CMS\CmsUser u where (u.id > 10) and (u.id between 1 and 10 or u.id in (1, 2, 3, 4, 5))", |
855
|
|
|
"SELECT c0_.id AS id_0, c0_.status AS status_1, c0_.username AS username_2, c0_.name AS name_3 FROM cms_users c0_ WHERE (c0_.id > 10) AND (c0_.id BETWEEN 1 AND 10 OR c0_.id IN (1, 2, 3, 4, 5))" |
856
|
|
|
); |
857
|
|
|
} |
858
|
|
|
|
859
|
|
|
public function testOrderByCollectionAssociationSize() |
860
|
|
|
{ |
861
|
|
|
$this->assertSqlGeneration( |
862
|
|
|
"select u, size(u.articles) as numArticles from Doctrine\Tests\Models\CMS\CmsUser u order by numArticles", |
863
|
|
|
"SELECT c0_.id AS id_0, c0_.status AS status_1, c0_.username AS username_2, c0_.name AS name_3, (SELECT COUNT(*) FROM cms_articles c1_ WHERE c1_.user_id = c0_.id) AS sclr_4 FROM cms_users c0_ ORDER BY sclr_4 ASC" |
864
|
|
|
); |
865
|
|
|
} |
866
|
|
|
|
867
|
|
|
public function testOrderBySupportsSingleValuedPathExpressionOwningSide() |
868
|
|
|
{ |
869
|
|
|
$this->assertSqlGeneration( |
870
|
|
|
"select a from Doctrine\Tests\Models\CMS\CmsArticle a order by a.user", |
871
|
|
|
"SELECT c0_.id AS id_0, c0_.topic AS topic_1, c0_.text AS text_2, c0_.version AS version_3 FROM cms_articles c0_ ORDER BY c0_.user_id ASC" |
872
|
|
|
); |
873
|
|
|
} |
874
|
|
|
|
875
|
|
|
/** |
876
|
|
|
* @expectedException Doctrine\ORM\Query\QueryException |
877
|
|
|
*/ |
878
|
|
|
public function testOrderBySupportsSingleValuedPathExpressionInverseSide() |
879
|
|
|
{ |
880
|
|
|
$q = $this->_em->createQuery("select u from Doctrine\Tests\Models\CMS\CmsUser u order by u.address"); |
881
|
|
|
$q->getSQL(); |
882
|
|
|
} |
883
|
|
|
|
884
|
|
|
public function testBooleanLiteralInWhereOnSqlite() |
885
|
|
|
{ |
886
|
|
|
$oldPlat = $this->_em->getConnection()->getDatabasePlatform(); |
887
|
|
|
$this->_em->getConnection()->setDatabasePlatform(new \Doctrine\DBAL\Platforms\SqlitePlatform); |
|
|
|
|
888
|
|
|
|
889
|
|
|
$this->assertSqlGeneration( |
890
|
|
|
"SELECT b FROM Doctrine\Tests\Models\Generic\BooleanModel b WHERE b.booleanField = true", |
891
|
|
|
"SELECT b0_.id AS id_0, b0_.booleanField AS booleanField_1 FROM boolean_model b0_ WHERE b0_.booleanField = 1" |
892
|
|
|
); |
893
|
|
|
|
894
|
|
|
$this->assertSqlGeneration( |
895
|
|
|
"SELECT b FROM Doctrine\Tests\Models\Generic\BooleanModel b WHERE b.booleanField = false", |
896
|
|
|
"SELECT b0_.id AS id_0, b0_.booleanField AS booleanField_1 FROM boolean_model b0_ WHERE b0_.booleanField = 0" |
897
|
|
|
); |
898
|
|
|
|
899
|
|
|
$this->_em->getConnection()->setDatabasePlatform($oldPlat); |
|
|
|
|
900
|
|
|
} |
901
|
|
|
|
902
|
|
|
public function testBooleanLiteralInWhereOnPostgres() |
903
|
|
|
{ |
904
|
|
|
$oldPlat = $this->_em->getConnection()->getDatabasePlatform(); |
905
|
|
|
$this->_em->getConnection()->setDatabasePlatform(new \Doctrine\DBAL\Platforms\PostgreSqlPlatform); |
|
|
|
|
906
|
|
|
|
907
|
|
|
$this->assertSqlGeneration( |
908
|
|
|
"SELECT b FROM Doctrine\Tests\Models\Generic\BooleanModel b WHERE b.booleanField = true", |
909
|
|
|
"SELECT b0_.id AS id_0, b0_.booleanField AS booleanfield_1 FROM boolean_model b0_ WHERE b0_.booleanField = true" |
910
|
|
|
); |
911
|
|
|
|
912
|
|
|
$this->assertSqlGeneration( |
913
|
|
|
"SELECT b FROM Doctrine\Tests\Models\Generic\BooleanModel b WHERE b.booleanField = false", |
914
|
|
|
"SELECT b0_.id AS id_0, b0_.booleanField AS booleanfield_1 FROM boolean_model b0_ WHERE b0_.booleanField = false" |
915
|
|
|
); |
916
|
|
|
|
917
|
|
|
$this->_em->getConnection()->setDatabasePlatform($oldPlat); |
|
|
|
|
918
|
|
|
} |
919
|
|
|
|
920
|
|
|
public function testSingleValuedAssociationFieldInWhere() |
921
|
|
|
{ |
922
|
|
|
$this->assertSqlGeneration( |
923
|
|
|
"SELECT p FROM Doctrine\Tests\Models\CMS\CmsPhonenumber p WHERE p.user = ?1", |
924
|
|
|
"SELECT c0_.phonenumber AS phonenumber_0 FROM cms_phonenumbers c0_ WHERE c0_.user_id = ?" |
925
|
|
|
); |
926
|
|
|
} |
927
|
|
|
|
928
|
|
|
public function testSingleValuedAssociationNullCheckOnOwningSide() |
929
|
|
|
{ |
930
|
|
|
$this->assertSqlGeneration( |
931
|
|
|
"SELECT a FROM Doctrine\Tests\Models\CMS\CmsAddress a WHERE a.user IS NULL", |
932
|
|
|
"SELECT c0_.id AS id_0, c0_.country AS country_1, c0_.zip AS zip_2, c0_.city AS city_3 FROM cms_addresses c0_ WHERE c0_.user_id IS NULL" |
933
|
|
|
); |
934
|
|
|
} |
935
|
|
|
|
936
|
|
|
// Null check on inverse side has to happen through explicit JOIN. |
937
|
|
|
// "SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE u.address IS NULL" |
938
|
|
|
// where the CmsUser is the inverse side is not supported. |
939
|
|
|
public function testSingleValuedAssociationNullCheckOnInverseSide() |
940
|
|
|
{ |
941
|
|
|
$this->assertSqlGeneration( |
942
|
|
|
"SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u LEFT JOIN u.address a WHERE a.id IS NULL", |
943
|
|
|
"SELECT c0_.id AS id_0, c0_.status AS status_1, c0_.username AS username_2, c0_.name AS name_3 FROM cms_users c0_ LEFT JOIN cms_addresses c1_ ON c0_.id = c1_.user_id WHERE c1_.id IS NULL" |
944
|
|
|
); |
945
|
|
|
} |
946
|
|
|
|
947
|
|
|
/** |
948
|
|
|
* @group DDC-339 |
949
|
|
|
* @group DDC-1572 |
950
|
|
|
*/ |
951
|
|
|
public function testStringFunctionLikeExpression() |
952
|
|
|
{ |
953
|
|
|
$this->assertSqlGeneration( |
954
|
|
|
"SELECT u.name FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE LOWER(u.name) LIKE '%foo OR bar%'", |
955
|
|
|
"SELECT c0_.name AS name_0 FROM cms_users c0_ WHERE LOWER(c0_.name) LIKE '%foo OR bar%'" |
956
|
|
|
); |
957
|
|
|
$this->assertSqlGeneration( |
958
|
|
|
"SELECT u.name FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE LOWER(u.name) LIKE :str", |
959
|
|
|
"SELECT c0_.name AS name_0 FROM cms_users c0_ WHERE LOWER(c0_.name) LIKE ?" |
960
|
|
|
); |
961
|
|
|
$this->assertSqlGeneration( |
962
|
|
|
"SELECT u.name FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE CONCAT(UPPER(u.name), '_moo') LIKE :str", |
963
|
|
|
"SELECT c0_.name AS name_0 FROM cms_users c0_ WHERE UPPER(c0_.name) || '_moo' LIKE ?" |
964
|
|
|
); |
965
|
|
|
|
966
|
|
|
// DDC-1572 |
967
|
|
|
$this->assertSqlGeneration( |
968
|
|
|
"SELECT u.name FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE UPPER(u.name) LIKE UPPER(:str)", |
969
|
|
|
"SELECT c0_.name AS name_0 FROM cms_users c0_ WHERE UPPER(c0_.name) LIKE UPPER(?)" |
970
|
|
|
); |
971
|
|
|
$this->assertSqlGeneration( |
972
|
|
|
"SELECT u.name FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE UPPER(LOWER(u.name)) LIKE UPPER(LOWER(:str))", |
973
|
|
|
"SELECT c0_.name AS name_0 FROM cms_users c0_ WHERE UPPER(LOWER(c0_.name)) LIKE UPPER(LOWER(?))" |
974
|
|
|
); |
975
|
|
|
$this->assertSqlGeneration( |
976
|
|
|
"SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u LEFT JOIN u.articles a WITH a.topic LIKE u.name", |
977
|
|
|
"SELECT c0_.id AS id_0, c0_.status AS status_1, c0_.username AS username_2, c0_.name AS name_3 FROM cms_users c0_ LEFT JOIN cms_articles c1_ ON c0_.id = c1_.user_id AND (c1_.topic LIKE c0_.name)" |
978
|
|
|
); |
979
|
|
|
} |
980
|
|
|
|
981
|
|
|
/** |
982
|
|
|
* @group DDC-1802 |
983
|
|
|
*/ |
984
|
|
|
public function testStringFunctionNotLikeExpression() |
985
|
|
|
{ |
986
|
|
|
$this->assertSqlGeneration( |
987
|
|
|
"SELECT u.name FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE LOWER(u.name) NOT LIKE '%foo OR bar%'", |
988
|
|
|
"SELECT c0_.name AS name_0 FROM cms_users c0_ WHERE LOWER(c0_.name) NOT LIKE '%foo OR bar%'" |
989
|
|
|
); |
990
|
|
|
|
991
|
|
|
$this->assertSqlGeneration( |
992
|
|
|
"SELECT u.name FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE UPPER(LOWER(u.name)) NOT LIKE UPPER(LOWER(:str))", |
993
|
|
|
"SELECT c0_.name AS name_0 FROM cms_users c0_ WHERE UPPER(LOWER(c0_.name)) NOT LIKE UPPER(LOWER(?))" |
994
|
|
|
); |
995
|
|
|
$this->assertSqlGeneration( |
996
|
|
|
"SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u LEFT JOIN u.articles a WITH a.topic NOT LIKE u.name", |
997
|
|
|
"SELECT c0_.id AS id_0, c0_.status AS status_1, c0_.username AS username_2, c0_.name AS name_3 FROM cms_users c0_ LEFT JOIN cms_articles c1_ ON c0_.id = c1_.user_id AND (c1_.topic NOT LIKE c0_.name)" |
998
|
|
|
); |
999
|
|
|
} |
1000
|
|
|
|
1001
|
|
|
/** |
1002
|
|
|
* @group DDC-338 |
1003
|
|
|
*/ |
1004
|
|
|
public function testOrderedCollectionFetchJoined() |
1005
|
|
|
{ |
1006
|
|
|
$this->assertSqlGeneration( |
1007
|
|
|
"SELECT r, l FROM Doctrine\Tests\Models\Routing\RoutingRoute r JOIN r.legs l", |
1008
|
|
|
"SELECT r0_.id AS id_0, r1_.id AS id_1, r1_.departureDate AS departureDate_2, r1_.arrivalDate AS arrivalDate_3 FROM RoutingRoute r0_ INNER JOIN RoutingRouteLegs r2_ ON r0_.id = r2_.route_id INNER JOIN RoutingLeg r1_ ON r1_.id = r2_.leg_id ". |
1009
|
|
|
"ORDER BY r1_.departureDate ASC" |
1010
|
|
|
); |
1011
|
|
|
} |
1012
|
|
|
|
1013
|
|
|
public function testSubselectInSelect() |
1014
|
|
|
{ |
1015
|
|
|
$this->assertSqlGeneration( |
1016
|
|
|
"SELECT u.name, (SELECT COUNT(p.phonenumber) FROM Doctrine\Tests\Models\CMS\CmsPhonenumber p WHERE p.phonenumber = 1234) pcount FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE u.name = 'jon'", |
1017
|
|
|
"SELECT c0_.name AS name_0, (SELECT COUNT(c1_.phonenumber) AS dctrn__1 FROM cms_phonenumbers c1_ WHERE c1_.phonenumber = 1234) AS sclr_1 FROM cms_users c0_ WHERE c0_.name = 'jon'" |
1018
|
|
|
); |
1019
|
|
|
} |
1020
|
|
|
|
1021
|
|
|
/** |
1022
|
|
|
* @group locking |
1023
|
|
|
* @group DDC-178 |
1024
|
|
|
*/ |
1025
|
|
|
public function testPessimisticWriteLockQueryHint() |
1026
|
|
|
{ |
1027
|
|
|
if ($this->_em->getConnection()->getDatabasePlatform() instanceof \Doctrine\DBAL\Platforms\SqlitePlatform) { |
1028
|
|
|
$this->markTestSkipped('SqLite does not support Row locking at all.'); |
1029
|
|
|
} |
1030
|
|
|
|
1031
|
|
|
$this->assertSqlGeneration( |
1032
|
|
|
"SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE u.username = 'gblanco'", |
1033
|
|
|
"SELECT c0_.id AS id_0, c0_.status AS status_1, c0_.username AS username_2, c0_.name AS name_3 ". |
1034
|
|
|
"FROM cms_users c0_ WHERE c0_.username = 'gblanco' FOR UPDATE", |
1035
|
|
|
array(Query::HINT_LOCK_MODE => \Doctrine\DBAL\LockMode::PESSIMISTIC_WRITE) |
1036
|
|
|
); |
1037
|
|
|
} |
1038
|
|
|
|
1039
|
|
|
/** |
1040
|
|
|
* @group locking |
1041
|
|
|
* @group DDC-178 |
1042
|
|
|
*/ |
1043
|
|
|
public function testPessimisticReadLockQueryHintPostgreSql() |
1044
|
|
|
{ |
1045
|
|
|
$this->_em->getConnection()->setDatabasePlatform(new \Doctrine\DBAL\Platforms\PostgreSqlPlatform); |
|
|
|
|
1046
|
|
|
|
1047
|
|
|
$this->assertSqlGeneration( |
1048
|
|
|
"SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE u.username = 'gblanco'", |
1049
|
|
|
"SELECT c0_.id AS id_0, c0_.status AS status_1, c0_.username AS username_2, c0_.name AS name_3 ". |
1050
|
|
|
"FROM cms_users c0_ WHERE c0_.username = 'gblanco' FOR SHARE", |
1051
|
|
|
array(Query::HINT_LOCK_MODE => \Doctrine\DBAL\LockMode::PESSIMISTIC_READ) |
1052
|
|
|
); |
1053
|
|
|
} |
1054
|
|
|
|
1055
|
|
|
/** |
1056
|
|
|
* @group DDC-1693 |
1057
|
|
|
* @group locking |
1058
|
|
|
*/ |
1059
|
|
|
public function testLockModeNoneQueryHint() |
1060
|
|
|
{ |
1061
|
|
|
$this->assertSqlGeneration( |
1062
|
|
|
"SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE u.username = 'gblanco'", |
1063
|
|
|
"SELECT c0_.id AS id_0, c0_.status AS status_1, c0_.username AS username_2, c0_.name AS name_3 ". |
1064
|
|
|
"FROM cms_users c0_ WHERE c0_.username = 'gblanco'", |
1065
|
|
|
array(Query::HINT_LOCK_MODE => \Doctrine\DBAL\LockMode::NONE) |
1066
|
|
|
); |
1067
|
|
|
} |
1068
|
|
|
|
1069
|
|
|
/** |
1070
|
|
|
* @group DDC-430 |
1071
|
|
|
*/ |
1072
|
|
|
public function testSupportSelectWithMoreThan10InputParameters() |
1073
|
|
|
{ |
1074
|
|
|
$this->assertSqlGeneration( |
1075
|
|
|
"SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE u.id = ?1 OR u.id = ?2 OR u.id = ?3 OR u.id = ?4 OR u.id = ?5 OR u.id = ?6 OR u.id = ?7 OR u.id = ?8 OR u.id = ?9 OR u.id = ?10 OR u.id = ?11", |
1076
|
|
|
"SELECT c0_.id AS id_0, c0_.status AS status_1, c0_.username AS username_2, c0_.name AS name_3 FROM cms_users c0_ WHERE c0_.id = ? OR c0_.id = ? OR c0_.id = ? OR c0_.id = ? OR c0_.id = ? OR c0_.id = ? OR c0_.id = ? OR c0_.id = ? OR c0_.id = ? OR c0_.id = ? OR c0_.id = ?" |
1077
|
|
|
); |
1078
|
|
|
} |
1079
|
|
|
|
1080
|
|
|
/** |
1081
|
|
|
* @group locking |
1082
|
|
|
* @group DDC-178 |
1083
|
|
|
*/ |
1084
|
|
|
public function testPessimisticReadLockQueryHintMySql() |
1085
|
|
|
{ |
1086
|
|
|
$this->_em->getConnection()->setDatabasePlatform(new \Doctrine\DBAL\Platforms\MySqlPlatform); |
|
|
|
|
1087
|
|
|
|
1088
|
|
|
$this->assertSqlGeneration( |
1089
|
|
|
"SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE u.username = 'gblanco'", |
1090
|
|
|
"SELECT c0_.id AS id_0, c0_.status AS status_1, c0_.username AS username_2, c0_.name AS name_3 ". |
1091
|
|
|
"FROM cms_users c0_ WHERE c0_.username = 'gblanco' LOCK IN SHARE MODE", |
1092
|
|
|
array(Query::HINT_LOCK_MODE => \Doctrine\DBAL\LockMode::PESSIMISTIC_READ) |
1093
|
|
|
); |
1094
|
|
|
} |
1095
|
|
|
|
1096
|
|
|
/** |
1097
|
|
|
* @group locking |
1098
|
|
|
* @group DDC-178 |
1099
|
|
|
*/ |
1100
|
|
|
public function testPessimisticReadLockQueryHintOracle() |
1101
|
|
|
{ |
1102
|
|
|
$this->_em->getConnection()->setDatabasePlatform(new \Doctrine\DBAL\Platforms\OraclePlatform); |
|
|
|
|
1103
|
|
|
|
1104
|
|
|
$this->assertSqlGeneration( |
1105
|
|
|
"SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE u.username = 'gblanco'", |
1106
|
|
|
"SELECT c0_.id AS ID_0, c0_.status AS STATUS_1, c0_.username AS USERNAME_2, c0_.name AS NAME_3 ". |
1107
|
|
|
"FROM cms_users c0_ WHERE c0_.username = 'gblanco' FOR UPDATE", |
1108
|
|
|
array(Query::HINT_LOCK_MODE => \Doctrine\DBAL\LockMode::PESSIMISTIC_READ) |
1109
|
|
|
); |
1110
|
|
|
} |
1111
|
|
|
|
1112
|
|
|
/** |
1113
|
|
|
* @group DDC-431 |
1114
|
|
|
*/ |
1115
|
|
|
public function testSupportToCustomDQLFunctions() |
1116
|
|
|
{ |
1117
|
|
|
$config = $this->_em->getConfiguration(); |
1118
|
|
|
$config->addCustomNumericFunction('MYABS', 'Doctrine\Tests\ORM\Query\MyAbsFunction'); |
1119
|
|
|
|
1120
|
|
|
$this->assertSqlGeneration( |
1121
|
|
|
'SELECT MYABS(p.phonenumber) FROM Doctrine\Tests\Models\CMS\CmsPhonenumber p', |
1122
|
|
|
'SELECT ABS(c0_.phonenumber) AS sclr_0 FROM cms_phonenumbers c0_' |
1123
|
|
|
); |
1124
|
|
|
|
1125
|
|
|
$config->setCustomNumericFunctions(array()); |
1126
|
|
|
} |
1127
|
|
|
|
1128
|
|
|
/** |
1129
|
|
|
* @group DDC-826 |
1130
|
|
|
*/ |
1131
|
|
|
public function testMappedSuperclassAssociationJoin() |
1132
|
|
|
{ |
1133
|
|
|
$this->assertSqlGeneration( |
1134
|
|
|
'SELECT f FROM Doctrine\Tests\Models\DirectoryTree\File f JOIN f.parentDirectory d WHERE f.id = ?1', |
1135
|
|
|
'SELECT f0_.id AS id_0, f0_.extension AS extension_1, f0_.name AS name_2 FROM "file" f0_ INNER JOIN Directory d1_ ON f0_.parentDirectory_id = d1_.id WHERE f0_.id = ?' |
1136
|
|
|
); |
1137
|
|
|
} |
1138
|
|
|
|
1139
|
|
|
/** |
1140
|
|
|
* @group DDC-1053 |
1141
|
|
|
*/ |
1142
|
|
|
public function testGroupBy() |
1143
|
|
|
{ |
1144
|
|
|
$this->assertSqlGeneration( |
1145
|
|
|
'SELECT g.id, count(u.id) FROM Doctrine\Tests\Models\CMS\CmsGroup g JOIN g.users u GROUP BY g.id', |
1146
|
|
|
'SELECT c0_.id AS id_0, count(c1_.id) AS sclr_1 FROM cms_groups c0_ INNER JOIN cms_users_groups c2_ ON c0_.id = c2_.group_id INNER JOIN cms_users c1_ ON c1_.id = c2_.user_id GROUP BY c0_.id' |
1147
|
|
|
); |
1148
|
|
|
} |
1149
|
|
|
|
1150
|
|
|
/** |
1151
|
|
|
* @group DDC-1053 |
1152
|
|
|
*/ |
1153
|
|
|
public function testGroupByIdentificationVariable() |
1154
|
|
|
{ |
1155
|
|
|
$this->assertSqlGeneration( |
1156
|
|
|
'SELECT g, count(u.id) FROM Doctrine\Tests\Models\CMS\CmsGroup g JOIN g.users u GROUP BY g', |
1157
|
|
|
'SELECT c0_.id AS id_0, c0_.name AS name_1, count(c1_.id) AS sclr_2 FROM cms_groups c0_ INNER JOIN cms_users_groups c2_ ON c0_.id = c2_.group_id INNER JOIN cms_users c1_ ON c1_.id = c2_.user_id GROUP BY c0_.id, c0_.name' |
1158
|
|
|
); |
1159
|
|
|
} |
1160
|
|
|
|
1161
|
|
|
public function testCaseContainingNullIf() |
1162
|
|
|
{ |
1163
|
|
|
$this->assertSqlGeneration( |
1164
|
|
|
"SELECT NULLIF(g.id, g.name) AS NullIfEqual FROM Doctrine\Tests\Models\CMS\CmsGroup g", |
1165
|
|
|
'SELECT NULLIF(c0_.id, c0_.name) AS sclr_0 FROM cms_groups c0_' |
1166
|
|
|
); |
1167
|
|
|
} |
1168
|
|
|
|
1169
|
|
|
public function testCaseContainingCoalesce() |
1170
|
|
|
{ |
1171
|
|
|
$this->assertSqlGeneration( |
1172
|
|
|
"SELECT COALESCE(NULLIF(u.name, ''), u.username) as Display FROM Doctrine\Tests\Models\CMS\CmsUser u", |
1173
|
|
|
"SELECT COALESCE(NULLIF(c0_.name, ''), c0_.username) AS sclr_0 FROM cms_users c0_" |
1174
|
|
|
); |
1175
|
|
|
} |
1176
|
|
|
|
1177
|
|
|
/** |
1178
|
|
|
* Test that the right discriminator data is inserted in a subquery. |
1179
|
|
|
*/ |
1180
|
|
|
public function testSubSelectDiscriminator() |
1181
|
|
|
{ |
1182
|
|
|
$this->assertSqlGeneration( |
1183
|
|
|
"SELECT u.name, (SELECT COUNT(cfc.id) total FROM Doctrine\Tests\Models\Company\CompanyFixContract cfc) as cfc_count FROM Doctrine\Tests\Models\CMS\CmsUser u", |
1184
|
|
|
"SELECT c0_.name AS name_0, (SELECT COUNT(c1_.id) AS dctrn__total FROM company_contracts c1_ WHERE c1_.discr IN ('fix')) AS sclr_1 FROM cms_users c0_" |
1185
|
|
|
); |
1186
|
|
|
} |
1187
|
|
|
|
1188
|
|
|
public function testIdVariableResultVariableReuse() |
1189
|
|
|
{ |
1190
|
|
|
$exceptionThrown = false; |
1191
|
|
|
try { |
1192
|
|
|
$query = $this->_em->createQuery("SELECT u.name FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE u.name IN (SELECT u.name FROM Doctrine\Tests\Models\CMS\CmsUser u)"); |
1193
|
|
|
|
1194
|
|
|
$query->getSql(); |
1195
|
|
|
$query->free(); |
1196
|
|
|
} catch (\Exception $e) { |
1197
|
|
|
$exceptionThrown = true; |
1198
|
|
|
} |
1199
|
|
|
|
1200
|
|
|
$this->assertTrue($exceptionThrown); |
1201
|
|
|
|
1202
|
|
|
} |
1203
|
|
|
|
1204
|
|
|
public function testSubSelectAliasesFromOuterQuery() |
1205
|
|
|
{ |
1206
|
|
|
$this->assertSqlGeneration( |
1207
|
|
|
"SELECT uo, (SELECT ui.name FROM Doctrine\Tests\Models\CMS\CmsUser ui WHERE ui.id = uo.id) AS bar FROM Doctrine\Tests\Models\CMS\CmsUser uo", |
1208
|
|
|
"SELECT c0_.id AS id_0, c0_.status AS status_1, c0_.username AS username_2, c0_.name AS name_3, (SELECT c1_.name FROM cms_users c1_ WHERE c1_.id = c0_.id) AS sclr_4 FROM cms_users c0_" |
1209
|
|
|
); |
1210
|
|
|
} |
1211
|
|
|
|
1212
|
|
|
public function testSubSelectAliasesFromOuterQueryWithSubquery() |
1213
|
|
|
{ |
1214
|
|
|
$this->assertSqlGeneration( |
1215
|
|
|
"SELECT uo, (SELECT ui.name FROM Doctrine\Tests\Models\CMS\CmsUser ui WHERE ui.id = uo.id AND ui.name IN (SELECT uii.name FROM Doctrine\Tests\Models\CMS\CmsUser uii)) AS bar FROM Doctrine\Tests\Models\CMS\CmsUser uo", |
1216
|
|
|
"SELECT c0_.id AS id_0, c0_.status AS status_1, c0_.username AS username_2, c0_.name AS name_3, (SELECT c1_.name FROM cms_users c1_ WHERE c1_.id = c0_.id AND c1_.name IN (SELECT c2_.name FROM cms_users c2_)) AS sclr_4 FROM cms_users c0_" |
1217
|
|
|
); |
1218
|
|
|
} |
1219
|
|
|
|
1220
|
|
|
public function testSubSelectAliasesFromOuterQueryReuseInWhereClause() |
1221
|
|
|
{ |
1222
|
|
|
$this->assertSqlGeneration( |
1223
|
|
|
"SELECT uo, (SELECT ui.name FROM Doctrine\Tests\Models\CMS\CmsUser ui WHERE ui.id = uo.id) AS bar FROM Doctrine\Tests\Models\CMS\CmsUser uo WHERE bar = ?0", |
1224
|
|
|
"SELECT c0_.id AS id_0, c0_.status AS status_1, c0_.username AS username_2, c0_.name AS name_3, (SELECT c1_.name FROM cms_users c1_ WHERE c1_.id = c0_.id) AS sclr_4 FROM cms_users c0_ WHERE sclr_4 = ?" |
1225
|
|
|
); |
1226
|
|
|
} |
1227
|
|
|
|
1228
|
|
|
/** |
1229
|
|
|
* @group DDC-1298 |
1230
|
|
|
*/ |
1231
|
|
|
public function testSelectForeignKeyPKWithoutFields() |
1232
|
|
|
{ |
1233
|
|
|
$this->assertSqlGeneration( |
1234
|
|
|
"SELECT t, s, l FROM Doctrine\Tests\Models\DDC117\DDC117Link l INNER JOIN l.target t INNER JOIN l.source s", |
1235
|
|
|
"SELECT d0_.article_id AS article_id_0, d0_.title AS title_1, d1_.article_id AS article_id_2, d1_.title AS title_3, d2_.source_id AS source_id_4, d2_.target_id AS target_id_5 FROM DDC117Link d2_ INNER JOIN DDC117Article d0_ ON d2_.target_id = d0_.article_id INNER JOIN DDC117Article d1_ ON d2_.source_id = d1_.article_id" |
1236
|
|
|
); |
1237
|
|
|
} |
1238
|
|
|
|
1239
|
|
|
public function testGeneralCaseWithSingleWhenClause() |
1240
|
|
|
{ |
1241
|
|
|
$this->assertSqlGeneration( |
1242
|
|
|
"SELECT g.id, CASE WHEN ((g.id / 2) > 18) THEN 1 ELSE 0 END AS test FROM Doctrine\Tests\Models\CMS\CmsGroup g", |
1243
|
|
|
"SELECT c0_.id AS id_0, CASE WHEN ((c0_.id / 2) > 18) THEN 1 ELSE 0 END AS sclr_1 FROM cms_groups c0_" |
1244
|
|
|
); |
1245
|
|
|
} |
1246
|
|
|
|
1247
|
|
|
public function testGeneralCaseWithMultipleWhenClause() |
1248
|
|
|
{ |
1249
|
|
|
$this->assertSqlGeneration( |
1250
|
|
|
"SELECT g.id, CASE WHEN (g.id / 2 < 10) THEN 2 WHEN ((g.id / 2) > 20) THEN 1 ELSE 0 END AS test FROM Doctrine\Tests\Models\CMS\CmsGroup g", |
1251
|
|
|
"SELECT c0_.id AS id_0, CASE WHEN (c0_.id / 2 < 10) THEN 2 WHEN ((c0_.id / 2) > 20) THEN 1 ELSE 0 END AS sclr_1 FROM cms_groups c0_" |
1252
|
|
|
); |
1253
|
|
|
} |
1254
|
|
|
|
1255
|
|
|
public function testSimpleCaseWithSingleWhenClause() |
1256
|
|
|
{ |
1257
|
|
|
$this->assertSqlGeneration( |
1258
|
|
|
"SELECT g FROM Doctrine\Tests\Models\CMS\CmsGroup g WHERE g.id = CASE g.name WHEN 'admin' THEN 1 ELSE 2 END", |
1259
|
|
|
"SELECT c0_.id AS id_0, c0_.name AS name_1 FROM cms_groups c0_ WHERE c0_.id = CASE c0_.name WHEN 'admin' THEN 1 ELSE 2 END" |
1260
|
|
|
); |
1261
|
|
|
} |
1262
|
|
|
|
1263
|
|
|
public function testSimpleCaseWithMultipleWhenClause() |
1264
|
|
|
{ |
1265
|
|
|
$this->assertSqlGeneration( |
1266
|
|
|
"SELECT g FROM Doctrine\Tests\Models\CMS\CmsGroup g WHERE g.id = (CASE g.name WHEN 'admin' THEN 1 WHEN 'moderator' THEN 2 ELSE 3 END)", |
1267
|
|
|
"SELECT c0_.id AS id_0, c0_.name AS name_1 FROM cms_groups c0_ WHERE c0_.id = (CASE c0_.name WHEN 'admin' THEN 1 WHEN 'moderator' THEN 2 ELSE 3 END)" |
1268
|
|
|
); |
1269
|
|
|
} |
1270
|
|
|
|
1271
|
|
|
public function testGeneralCaseWithSingleWhenClauseInSubselect() |
1272
|
|
|
{ |
1273
|
|
|
$this->assertSqlGeneration( |
1274
|
|
|
"SELECT g FROM Doctrine\Tests\Models\CMS\CmsGroup g WHERE g.id IN (SELECT CASE WHEN ((g2.id / 2) > 18) THEN 2 ELSE 1 END FROM Doctrine\Tests\Models\CMS\CmsGroup g2)", |
1275
|
|
|
"SELECT c0_.id AS id_0, c0_.name AS name_1 FROM cms_groups c0_ WHERE c0_.id IN (SELECT CASE WHEN ((c1_.id / 2) > 18) THEN 2 ELSE 1 END AS sclr_2 FROM cms_groups c1_)" |
1276
|
|
|
); |
1277
|
|
|
} |
1278
|
|
|
|
1279
|
|
|
public function testGeneralCaseWithMultipleWhenClauseInSubselect() |
1280
|
|
|
{ |
1281
|
|
|
$this->assertSqlGeneration( |
1282
|
|
|
"SELECT g FROM Doctrine\Tests\Models\CMS\CmsGroup g WHERE g.id IN (SELECT CASE WHEN (g.id / 2 < 10) THEN 3 WHEN ((g.id / 2) > 20) THEN 2 ELSE 1 END FROM Doctrine\Tests\Models\CMS\CmsGroup g2)", |
1283
|
|
|
"SELECT c0_.id AS id_0, c0_.name AS name_1 FROM cms_groups c0_ WHERE c0_.id IN (SELECT CASE WHEN (c0_.id / 2 < 10) THEN 3 WHEN ((c0_.id / 2) > 20) THEN 2 ELSE 1 END AS sclr_2 FROM cms_groups c1_)" |
1284
|
|
|
); |
1285
|
|
|
} |
1286
|
|
|
|
1287
|
|
|
public function testSimpleCaseWithSingleWhenClauseInSubselect() |
1288
|
|
|
{ |
1289
|
|
|
$this->assertSqlGeneration( |
1290
|
|
|
"SELECT g FROM Doctrine\Tests\Models\CMS\CmsGroup g WHERE g.id IN (SELECT CASE g2.name WHEN 'admin' THEN 1 ELSE 2 END FROM Doctrine\Tests\Models\CMS\CmsGroup g2)", |
1291
|
|
|
"SELECT c0_.id AS id_0, c0_.name AS name_1 FROM cms_groups c0_ WHERE c0_.id IN (SELECT CASE c1_.name WHEN 'admin' THEN 1 ELSE 2 END AS sclr_2 FROM cms_groups c1_)" |
1292
|
|
|
); |
1293
|
|
|
} |
1294
|
|
|
|
1295
|
|
|
public function testSimpleCaseWithMultipleWhenClauseInSubselect() |
1296
|
|
|
{ |
1297
|
|
|
$this->assertSqlGeneration( |
1298
|
|
|
"SELECT g FROM Doctrine\Tests\Models\CMS\CmsGroup g WHERE g.id IN (SELECT CASE g2.name WHEN 'admin' THEN 1 WHEN 'moderator' THEN 2 ELSE 3 END FROM Doctrine\Tests\Models\CMS\CmsGroup g2)", |
1299
|
|
|
"SELECT c0_.id AS id_0, c0_.name AS name_1 FROM cms_groups c0_ WHERE c0_.id IN (SELECT CASE c1_.name WHEN 'admin' THEN 1 WHEN 'moderator' THEN 2 ELSE 3 END AS sclr_2 FROM cms_groups c1_)" |
1300
|
|
|
); |
1301
|
|
|
} |
1302
|
|
|
|
1303
|
|
|
/** |
1304
|
|
|
* @group DDC-1696 |
1305
|
|
|
*/ |
1306
|
|
|
public function testSimpleCaseWithStringPrimary() |
1307
|
|
|
{ |
1308
|
|
|
$this->assertSqlGeneration( |
1309
|
|
|
"SELECT g.id, CASE WHEN ((g.id / 2) > 18) THEN 'Foo' ELSE 'Bar' END AS test FROM Doctrine\Tests\Models\CMS\CmsGroup g", |
1310
|
|
|
"SELECT c0_.id AS id_0, CASE WHEN ((c0_.id / 2) > 18) THEN 'Foo' ELSE 'Bar' END AS sclr_1 FROM cms_groups c0_" |
1311
|
|
|
); |
1312
|
|
|
} |
1313
|
|
|
|
1314
|
|
|
/** |
1315
|
|
|
* @group DDC-2205 |
1316
|
|
|
*/ |
1317
|
|
|
public function testCaseNegativeValuesInThenExpression() |
1318
|
|
|
{ |
1319
|
|
|
$this->assertSqlGeneration( |
1320
|
|
|
"SELECT CASE g.name WHEN 'admin' THEN - 1 ELSE - 2 END FROM Doctrine\Tests\Models\CMS\CmsGroup g", |
1321
|
|
|
"SELECT CASE c0_.name WHEN 'admin' THEN -1 ELSE -2 END AS sclr_0 FROM cms_groups c0_" |
1322
|
|
|
); |
1323
|
|
|
|
1324
|
|
|
$this->assertSqlGeneration( |
1325
|
|
|
"SELECT CASE g.name WHEN 'admin' THEN - 2 WHEN 'guest' THEN - 1 ELSE 0 END FROM Doctrine\Tests\Models\CMS\CmsGroup g", |
1326
|
|
|
"SELECT CASE c0_.name WHEN 'admin' THEN -2 WHEN 'guest' THEN -1 ELSE 0 END AS sclr_0 FROM cms_groups c0_" |
1327
|
|
|
); |
1328
|
|
|
|
1329
|
|
|
$this->assertSqlGeneration( |
1330
|
|
|
"SELECT CASE g.name WHEN 'admin' THEN (- 1) ELSE (- 2) END FROM Doctrine\Tests\Models\CMS\CmsGroup g", |
1331
|
|
|
"SELECT CASE c0_.name WHEN 'admin' THEN (-1) ELSE (-2) END AS sclr_0 FROM cms_groups c0_" |
1332
|
|
|
); |
1333
|
|
|
|
1334
|
|
|
$this->assertSqlGeneration( |
1335
|
|
|
"SELECT CASE g.name WHEN 'admin' THEN ( - :value) ELSE ( + :value) END FROM Doctrine\Tests\Models\CMS\CmsGroup g", |
1336
|
|
|
"SELECT CASE c0_.name WHEN 'admin' THEN (-?) ELSE (+?) END AS sclr_0 FROM cms_groups c0_" |
1337
|
|
|
); |
1338
|
|
|
|
1339
|
|
|
$this->assertSqlGeneration( |
1340
|
|
|
"SELECT CASE g.name WHEN 'admin' THEN ( - g.id) ELSE ( + g.id) END FROM Doctrine\Tests\Models\CMS\CmsGroup g", |
1341
|
|
|
"SELECT CASE c0_.name WHEN 'admin' THEN (-c0_.id) ELSE (+c0_.id) END AS sclr_0 FROM cms_groups c0_" |
1342
|
|
|
); |
1343
|
|
|
} |
1344
|
|
|
|
1345
|
|
|
public function testIdentityFunctionWithCompositePrimaryKey() |
1346
|
|
|
{ |
1347
|
|
|
$this->assertSqlGeneration( |
1348
|
|
|
"SELECT IDENTITY(p.poi, 'long') AS long FROM Doctrine\Tests\Models\Navigation\NavPhotos p", |
1349
|
|
|
"SELECT n0_.poi_long AS sclr_0 FROM navigation_photos n0_" |
1350
|
|
|
); |
1351
|
|
|
|
1352
|
|
|
$this->assertSqlGeneration( |
1353
|
|
|
"SELECT IDENTITY(p.poi, 'lat') AS lat FROM Doctrine\Tests\Models\Navigation\NavPhotos p", |
1354
|
|
|
"SELECT n0_.poi_lat AS sclr_0 FROM navigation_photos n0_" |
1355
|
|
|
); |
1356
|
|
|
|
1357
|
|
|
$this->assertSqlGeneration( |
1358
|
|
|
"SELECT IDENTITY(p.poi, 'long') AS long, IDENTITY(p.poi, 'lat') AS lat FROM Doctrine\Tests\Models\Navigation\NavPhotos p", |
1359
|
|
|
"SELECT n0_.poi_long AS sclr_0, n0_.poi_lat AS sclr_1 FROM navigation_photos n0_" |
1360
|
|
|
); |
1361
|
|
|
|
1362
|
|
|
$this->assertInvalidSqlGeneration( |
1363
|
|
|
"SELECT IDENTITY(p.poi, 'invalid') AS invalid FROM Doctrine\Tests\Models\Navigation\NavPhotos p", |
1364
|
|
|
"Doctrine\ORM\Query\QueryException" |
1365
|
|
|
); |
1366
|
|
|
} |
1367
|
|
|
|
1368
|
|
|
/** |
1369
|
|
|
* @group DDC-2519 |
1370
|
|
|
*/ |
1371
|
|
|
public function testPartialWithAssociationIdentifier() |
1372
|
|
|
{ |
1373
|
|
|
$this->assertSqlGeneration( |
1374
|
|
|
"SELECT PARTIAL l.{_source, _target} FROM Doctrine\Tests\Models\Legacy\LegacyUserReference l", |
1375
|
|
|
'SELECT l0_.iUserIdSource AS iUserIdSource_0, l0_.iUserIdTarget AS iUserIdTarget_1 FROM legacy_users_reference l0_' |
1376
|
|
|
); |
1377
|
|
|
|
1378
|
|
|
$this->assertSqlGeneration( |
1379
|
|
|
"SELECT PARTIAL l.{_description, _source, _target} FROM Doctrine\Tests\Models\Legacy\LegacyUserReference l", |
1380
|
|
|
'SELECT l0_.description AS description_0, l0_.iUserIdSource AS iUserIdSource_1, l0_.iUserIdTarget AS iUserIdTarget_2 FROM legacy_users_reference l0_' |
1381
|
|
|
); |
1382
|
|
|
} |
1383
|
|
|
|
1384
|
|
|
/** |
1385
|
|
|
* @group DDC-1339 |
1386
|
|
|
*/ |
1387
|
|
|
public function testIdentityFunctionInSelectClause() |
1388
|
|
|
{ |
1389
|
|
|
$this->assertSqlGeneration( |
1390
|
|
|
"SELECT IDENTITY(u.email) as email_id FROM Doctrine\Tests\Models\CMS\CmsUser u", |
1391
|
|
|
"SELECT c0_.email_id AS sclr_0 FROM cms_users c0_" |
1392
|
|
|
); |
1393
|
|
|
} |
1394
|
|
|
|
1395
|
|
|
public function testIdentityFunctionInJoinedSubclass() |
1396
|
|
|
{ |
1397
|
|
|
//relation is in the subclass (CompanyManager) we are querying |
1398
|
|
|
$this->assertSqlGeneration( |
1399
|
|
|
'SELECT m, IDENTITY(m.car) as car_id FROM Doctrine\Tests\Models\Company\CompanyManager m', |
1400
|
|
|
'SELECT c0_.id AS id_0, c0_.name AS name_1, c1_.salary AS salary_2, c1_.department AS department_3, c1_.startDate AS startDate_4, c2_.title AS title_5, c2_.car_id AS sclr_6, c0_.discr AS discr_7 FROM company_managers c2_ INNER JOIN company_employees c1_ ON c2_.id = c1_.id INNER JOIN company_persons c0_ ON c2_.id = c0_.id' |
1401
|
|
|
); |
1402
|
|
|
|
1403
|
|
|
//relation is in the base class (CompanyPerson). |
1404
|
|
|
$this->assertSqlGeneration( |
1405
|
|
|
'SELECT m, IDENTITY(m.spouse) as spouse_id FROM Doctrine\Tests\Models\Company\CompanyManager m', |
1406
|
|
|
'SELECT c0_.id AS id_0, c0_.name AS name_1, c1_.salary AS salary_2, c1_.department AS department_3, c1_.startDate AS startDate_4, c2_.title AS title_5, c0_.spouse_id AS sclr_6, c0_.discr AS discr_7 FROM company_managers c2_ INNER JOIN company_employees c1_ ON c2_.id = c1_.id INNER JOIN company_persons c0_ ON c2_.id = c0_.id' |
1407
|
|
|
); |
1408
|
|
|
} |
1409
|
|
|
|
1410
|
|
|
/** |
1411
|
|
|
* @group DDC-1339 |
1412
|
|
|
*/ |
1413
|
|
|
public function testIdentityFunctionDoesNotAcceptStateField() |
1414
|
|
|
{ |
1415
|
|
|
$this->assertInvalidSqlGeneration( |
1416
|
|
|
"SELECT IDENTITY(u.name) as name FROM Doctrine\Tests\Models\CMS\CmsUser u", |
1417
|
|
|
"Doctrine\ORM\Query\QueryException" |
1418
|
|
|
); |
1419
|
|
|
} |
1420
|
|
|
|
1421
|
|
|
/** |
1422
|
|
|
* @group DDC-1389 |
1423
|
|
|
*/ |
1424
|
|
|
public function testInheritanceTypeJoinInRootClassWithDisabledForcePartialLoad() |
1425
|
|
|
{ |
1426
|
|
|
$this->assertSqlGeneration( |
1427
|
|
|
'SELECT p FROM Doctrine\Tests\Models\Company\CompanyPerson p', |
1428
|
|
|
'SELECT c0_.id AS id_0, c0_.name AS name_1, c1_.title AS title_2, c2_.salary AS salary_3, c2_.department AS department_4, c2_.startDate AS startDate_5, c0_.discr AS discr_6, c0_.spouse_id AS spouse_id_7, c1_.car_id AS car_id_8 FROM company_persons c0_ LEFT JOIN company_managers c1_ ON c0_.id = c1_.id LEFT JOIN company_employees c2_ ON c0_.id = c2_.id', |
1429
|
|
|
array(Query::HINT_FORCE_PARTIAL_LOAD => false) |
1430
|
|
|
); |
1431
|
|
|
} |
1432
|
|
|
|
1433
|
|
|
/** |
1434
|
|
|
* @group DDC-1389 |
1435
|
|
|
*/ |
1436
|
|
|
public function testInheritanceTypeJoinInRootClassWithEnabledForcePartialLoad() |
1437
|
|
|
{ |
1438
|
|
|
$this->assertSqlGeneration( |
1439
|
|
|
'SELECT p FROM Doctrine\Tests\Models\Company\CompanyPerson p', |
1440
|
|
|
'SELECT c0_.id AS id_0, c0_.name AS name_1, c0_.discr AS discr_2 FROM company_persons c0_', |
1441
|
|
|
array(Query::HINT_FORCE_PARTIAL_LOAD => true) |
1442
|
|
|
); |
1443
|
|
|
} |
1444
|
|
|
|
1445
|
|
|
/** |
1446
|
|
|
* @group DDC-1389 |
1447
|
|
|
*/ |
1448
|
|
|
public function testInheritanceTypeJoinInChildClassWithDisabledForcePartialLoad() |
1449
|
|
|
{ |
1450
|
|
|
$this->assertSqlGeneration( |
1451
|
|
|
'SELECT e FROM Doctrine\Tests\Models\Company\CompanyEmployee e', |
1452
|
|
|
'SELECT c0_.id AS id_0, c0_.name AS name_1, c1_.salary AS salary_2, c1_.department AS department_3, c1_.startDate AS startDate_4, c2_.title AS title_5, c0_.discr AS discr_6, c0_.spouse_id AS spouse_id_7, c2_.car_id AS car_id_8 FROM company_employees c1_ INNER JOIN company_persons c0_ ON c1_.id = c0_.id LEFT JOIN company_managers c2_ ON c1_.id = c2_.id', |
1453
|
|
|
array(Query::HINT_FORCE_PARTIAL_LOAD => false) |
1454
|
|
|
); |
1455
|
|
|
} |
1456
|
|
|
|
1457
|
|
|
/** |
1458
|
|
|
* @group DDC-1389 |
1459
|
|
|
*/ |
1460
|
|
|
public function testInheritanceTypeJoinInChildClassWithEnabledForcePartialLoad() |
1461
|
|
|
{ |
1462
|
|
|
$this->assertSqlGeneration( |
1463
|
|
|
'SELECT e FROM Doctrine\Tests\Models\Company\CompanyEmployee e', |
1464
|
|
|
'SELECT c0_.id AS id_0, c0_.name AS name_1, c1_.salary AS salary_2, c1_.department AS department_3, c1_.startDate AS startDate_4, c0_.discr AS discr_5 FROM company_employees c1_ INNER JOIN company_persons c0_ ON c1_.id = c0_.id', |
1465
|
|
|
array(Query::HINT_FORCE_PARTIAL_LOAD => true) |
1466
|
|
|
); |
1467
|
|
|
} |
1468
|
|
|
|
1469
|
|
|
/** |
1470
|
|
|
* @group DDC-1389 |
1471
|
|
|
*/ |
1472
|
|
|
public function testInheritanceTypeJoinInLeafClassWithDisabledForcePartialLoad() |
1473
|
|
|
{ |
1474
|
|
|
$this->assertSqlGeneration( |
1475
|
|
|
'SELECT m FROM Doctrine\Tests\Models\Company\CompanyManager m', |
1476
|
|
|
'SELECT c0_.id AS id_0, c0_.name AS name_1, c1_.salary AS salary_2, c1_.department AS department_3, c1_.startDate AS startDate_4, c2_.title AS title_5, c0_.discr AS discr_6, c0_.spouse_id AS spouse_id_7, c2_.car_id AS car_id_8 FROM company_managers c2_ INNER JOIN company_employees c1_ ON c2_.id = c1_.id INNER JOIN company_persons c0_ ON c2_.id = c0_.id', |
1477
|
|
|
array(Query::HINT_FORCE_PARTIAL_LOAD => false) |
1478
|
|
|
); |
1479
|
|
|
} |
1480
|
|
|
|
1481
|
|
|
/** |
1482
|
|
|
* @group DDC-1389 |
1483
|
|
|
*/ |
1484
|
|
|
public function testInheritanceTypeJoinInLeafClassWithEnabledForcePartialLoad() |
1485
|
|
|
{ |
1486
|
|
|
$this->assertSqlGeneration( |
1487
|
|
|
'SELECT m FROM Doctrine\Tests\Models\Company\CompanyManager m', |
1488
|
|
|
'SELECT c0_.id AS id_0, c0_.name AS name_1, c1_.salary AS salary_2, c1_.department AS department_3, c1_.startDate AS startDate_4, c2_.title AS title_5, c0_.discr AS discr_6 FROM company_managers c2_ INNER JOIN company_employees c1_ ON c2_.id = c1_.id INNER JOIN company_persons c0_ ON c2_.id = c0_.id', |
1489
|
|
|
array(Query::HINT_FORCE_PARTIAL_LOAD => true) |
1490
|
|
|
); |
1491
|
|
|
} |
1492
|
|
|
|
1493
|
|
|
/** |
1494
|
|
|
* @group DDC-1389 |
1495
|
|
|
*/ |
1496
|
|
|
public function testInheritanceTypeSingleTableInRootClassWithDisabledForcePartialLoad() |
1497
|
|
|
{ |
1498
|
|
|
$this->assertSqlGeneration( |
1499
|
|
|
'SELECT c FROM Doctrine\Tests\Models\Company\CompanyContract c', |
1500
|
|
|
"SELECT c0_.id AS id_0, c0_.completed AS completed_1, c0_.fixPrice AS fixPrice_2, c0_.hoursWorked AS hoursWorked_3, c0_.pricePerHour AS pricePerHour_4, c0_.maxPrice AS maxPrice_5, c0_.discr AS discr_6, c0_.salesPerson_id AS salesPerson_id_7 FROM company_contracts c0_ WHERE c0_.discr IN ('fix', 'flexible', 'flexultra')", |
1501
|
|
|
array(Query::HINT_FORCE_PARTIAL_LOAD => false) |
1502
|
|
|
); |
1503
|
|
|
} |
1504
|
|
|
|
1505
|
|
|
/** |
1506
|
|
|
* @group DDC-1389 |
1507
|
|
|
*/ |
1508
|
|
|
public function testInheritanceTypeSingleTableInRootClassWithEnabledForcePartialLoad() |
1509
|
|
|
{ |
1510
|
|
|
$this->assertSqlGeneration( |
1511
|
|
|
'SELECT c FROM Doctrine\Tests\Models\Company\CompanyContract c', |
1512
|
|
|
"SELECT c0_.id AS id_0, c0_.completed AS completed_1, c0_.fixPrice AS fixPrice_2, c0_.hoursWorked AS hoursWorked_3, c0_.pricePerHour AS pricePerHour_4, c0_.maxPrice AS maxPrice_5, c0_.discr AS discr_6 FROM company_contracts c0_ WHERE c0_.discr IN ('fix', 'flexible', 'flexultra')", |
1513
|
|
|
array(Query::HINT_FORCE_PARTIAL_LOAD => true) |
1514
|
|
|
); |
1515
|
|
|
} |
1516
|
|
|
|
1517
|
|
|
/** |
1518
|
|
|
* @group DDC-1389 |
1519
|
|
|
*/ |
1520
|
|
|
public function testInheritanceTypeSingleTableInChildClassWithDisabledForcePartialLoad() |
1521
|
|
|
{ |
1522
|
|
|
$this->assertSqlGeneration( |
1523
|
|
|
'SELECT fc FROM Doctrine\Tests\Models\Company\CompanyFlexContract fc', |
1524
|
|
|
"SELECT c0_.id AS id_0, c0_.completed AS completed_1, c0_.hoursWorked AS hoursWorked_2, c0_.pricePerHour AS pricePerHour_3, c0_.maxPrice AS maxPrice_4, c0_.discr AS discr_5, c0_.salesPerson_id AS salesPerson_id_6 FROM company_contracts c0_ WHERE c0_.discr IN ('flexible', 'flexultra')", |
1525
|
|
|
array(Query::HINT_FORCE_PARTIAL_LOAD => false) |
1526
|
|
|
); |
1527
|
|
|
} |
1528
|
|
|
|
1529
|
|
|
/** |
1530
|
|
|
* @group DDC-1389 |
1531
|
|
|
*/ |
1532
|
|
|
public function testInheritanceTypeSingleTableInChildClassWithEnabledForcePartialLoad() |
1533
|
|
|
{ |
1534
|
|
|
$this->assertSqlGeneration( |
1535
|
|
|
'SELECT fc FROM Doctrine\Tests\Models\Company\CompanyFlexContract fc', |
1536
|
|
|
"SELECT c0_.id AS id_0, c0_.completed AS completed_1, c0_.hoursWorked AS hoursWorked_2, c0_.pricePerHour AS pricePerHour_3, c0_.maxPrice AS maxPrice_4, c0_.discr AS discr_5 FROM company_contracts c0_ WHERE c0_.discr IN ('flexible', 'flexultra')", |
1537
|
|
|
array(Query::HINT_FORCE_PARTIAL_LOAD => true) |
1538
|
|
|
); |
1539
|
|
|
} |
1540
|
|
|
|
1541
|
|
|
/** |
1542
|
|
|
* @group DDC-1389 |
1543
|
|
|
*/ |
1544
|
|
|
public function testInheritanceTypeSingleTableInLeafClassWithDisabledForcePartialLoad() |
1545
|
|
|
{ |
1546
|
|
|
$this->assertSqlGeneration( |
1547
|
|
|
'SELECT fuc FROM Doctrine\Tests\Models\Company\CompanyFlexUltraContract fuc', |
1548
|
|
|
"SELECT c0_.id AS id_0, c0_.completed AS completed_1, c0_.hoursWorked AS hoursWorked_2, c0_.pricePerHour AS pricePerHour_3, c0_.maxPrice AS maxPrice_4, c0_.discr AS discr_5, c0_.salesPerson_id AS salesPerson_id_6 FROM company_contracts c0_ WHERE c0_.discr IN ('flexultra')", |
1549
|
|
|
array(Query::HINT_FORCE_PARTIAL_LOAD => false) |
1550
|
|
|
); |
1551
|
|
|
} |
1552
|
|
|
|
1553
|
|
|
/** |
1554
|
|
|
* @group DDC-1389 |
1555
|
|
|
*/ |
1556
|
|
|
public function testInheritanceTypeSingleTableInLeafClassWithEnabledForcePartialLoad() |
1557
|
|
|
{ |
1558
|
|
|
$this->assertSqlGeneration( |
1559
|
|
|
'SELECT fuc FROM Doctrine\Tests\Models\Company\CompanyFlexUltraContract fuc', |
1560
|
|
|
"SELECT c0_.id AS id_0, c0_.completed AS completed_1, c0_.hoursWorked AS hoursWorked_2, c0_.pricePerHour AS pricePerHour_3, c0_.maxPrice AS maxPrice_4, c0_.discr AS discr_5 FROM company_contracts c0_ WHERE c0_.discr IN ('flexultra')", |
1561
|
|
|
array(Query::HINT_FORCE_PARTIAL_LOAD => true) |
1562
|
|
|
); |
1563
|
|
|
} |
1564
|
|
|
|
1565
|
|
|
/** |
1566
|
|
|
* @group DDC-1161 |
1567
|
|
|
*/ |
1568
|
|
|
public function testSelfReferenceWithOneToOneDoesNotDuplicateAlias() |
1569
|
|
|
{ |
1570
|
|
|
$this->assertSqlGeneration( |
1571
|
|
|
'SELECT p, pp FROM Doctrine\Tests\Models\Company\CompanyPerson p JOIN p.spouse pp', |
1572
|
|
|
"SELECT c0_.id AS id_0, c0_.name AS name_1, c1_.title AS title_2, c2_.salary AS salary_3, c2_.department AS department_4, c2_.startDate AS startDate_5, c3_.id AS id_6, c3_.name AS name_7, c4_.title AS title_8, c5_.salary AS salary_9, c5_.department AS department_10, c5_.startDate AS startDate_11, c0_.discr AS discr_12, c0_.spouse_id AS spouse_id_13, c1_.car_id AS car_id_14, c3_.discr AS discr_15, c3_.spouse_id AS spouse_id_16, c4_.car_id AS car_id_17 FROM company_persons c0_ LEFT JOIN company_managers c1_ ON c0_.id = c1_.id LEFT JOIN company_employees c2_ ON c0_.id = c2_.id INNER JOIN company_persons c3_ ON c0_.spouse_id = c3_.id LEFT JOIN company_managers c4_ ON c3_.id = c4_.id LEFT JOIN company_employees c5_ ON c3_.id = c5_.id", |
1573
|
|
|
array(Query::HINT_FORCE_PARTIAL_LOAD => false) |
1574
|
|
|
); |
1575
|
|
|
} |
1576
|
|
|
|
1577
|
|
|
/** |
1578
|
|
|
* @group DDC-1384 |
1579
|
|
|
*/ |
1580
|
|
|
public function testAliasDoesNotExceedPlatformDefinedLength() |
1581
|
|
|
{ |
1582
|
|
|
$this->assertSqlGeneration( |
1583
|
|
|
'SELECT m FROM ' . __NAMESPACE__ . '\\DDC1384Model m', |
1584
|
|
|
"SELECT d0_.aVeryLongIdentifierThatShouldBeShortenedByTheSQLWalker_fooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo AS ooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo_0 FROM DDC1384Model d0_" |
1585
|
|
|
); |
1586
|
|
|
} |
1587
|
|
|
|
1588
|
|
|
/** |
1589
|
|
|
* @group DDC-331 |
1590
|
|
|
* @group DDC-1384 |
1591
|
|
|
*/ |
1592
|
|
|
public function testIssue331() |
1593
|
|
|
{ |
1594
|
|
|
$this->assertSqlGeneration( |
1595
|
|
|
'SELECT e.name FROM Doctrine\Tests\Models\Company\CompanyEmployee e', |
1596
|
|
|
'SELECT c0_.name AS name_0 FROM company_employees c1_ INNER JOIN company_persons c0_ ON c1_.id = c0_.id' |
1597
|
|
|
); |
1598
|
|
|
} |
1599
|
|
|
/** |
1600
|
|
|
* @group DDC-1435 |
1601
|
|
|
*/ |
1602
|
|
|
public function testForeignKeyAsPrimaryKeySubselect() |
1603
|
|
|
{ |
1604
|
|
|
$this->assertSqlGeneration( |
1605
|
|
|
"SELECT s FROM Doctrine\Tests\Models\DDC117\DDC117Article s WHERE EXISTS (SELECT r FROM Doctrine\Tests\Models\DDC117\DDC117Reference r WHERE r.source = s)", |
1606
|
|
|
"SELECT d0_.article_id AS article_id_0, d0_.title AS title_1 FROM DDC117Article d0_ WHERE EXISTS (SELECT d1_.source_id, d1_.target_id FROM DDC117Reference d1_ WHERE d1_.source_id = d0_.article_id)" |
1607
|
|
|
); |
1608
|
|
|
} |
1609
|
|
|
|
1610
|
|
|
/** |
1611
|
|
|
* @group DDC-1474 |
1612
|
|
|
*/ |
1613
|
|
|
public function testSelectWithArithmeticExpressionBeforeField() |
1614
|
|
|
{ |
1615
|
|
|
$this->assertSqlGeneration( |
1616
|
|
|
'SELECT - e.value AS value, e.id FROM ' . __NAMESPACE__ . '\DDC1474Entity e', |
1617
|
|
|
'SELECT -d0_.value AS sclr_0, d0_.id AS id_1 FROM DDC1474Entity d0_' |
1618
|
|
|
); |
1619
|
|
|
|
1620
|
|
|
$this->assertSqlGeneration( |
1621
|
|
|
'SELECT e.id, + e.value AS value FROM ' . __NAMESPACE__ . '\DDC1474Entity e', |
1622
|
|
|
'SELECT d0_.id AS id_0, +d0_.value AS sclr_1 FROM DDC1474Entity d0_' |
1623
|
|
|
); |
1624
|
|
|
} |
1625
|
|
|
|
1626
|
|
|
/** |
1627
|
|
|
* @group DDC-1430 |
1628
|
|
|
*/ |
1629
|
|
|
public function testGroupByAllFieldsWhenObjectHasForeignKeys() |
1630
|
|
|
{ |
1631
|
|
|
$this->assertSqlGeneration( |
1632
|
|
|
'SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u GROUP BY u', |
1633
|
|
|
'SELECT c0_.id AS id_0, c0_.status AS status_1, c0_.username AS username_2, c0_.name AS name_3 FROM cms_users c0_ GROUP BY c0_.id, c0_.status, c0_.username, c0_.name, c0_.email_id' |
1634
|
|
|
); |
1635
|
|
|
|
1636
|
|
|
$this->assertSqlGeneration( |
1637
|
|
|
'SELECT e FROM Doctrine\Tests\Models\CMS\CmsEmployee e GROUP BY e', |
1638
|
|
|
'SELECT c0_.id AS id_0, c0_.name AS name_1 FROM cms_employees c0_ GROUP BY c0_.id, c0_.name, c0_.spouse_id' |
1639
|
|
|
); |
1640
|
|
|
} |
1641
|
|
|
|
1642
|
|
|
/** |
1643
|
|
|
* @group DDC-1236 |
1644
|
|
|
*/ |
1645
|
|
|
public function testGroupBySupportsResultVariable() |
1646
|
|
|
{ |
1647
|
|
|
$this->assertSqlGeneration( |
1648
|
|
|
'SELECT u, u.status AS st FROM Doctrine\Tests\Models\CMS\CmsUser u GROUP BY st', |
1649
|
|
|
'SELECT c0_.id AS id_0, c0_.status AS status_1, c0_.username AS username_2, c0_.name AS name_3, c0_.status AS status_4 FROM cms_users c0_ GROUP BY c0_.status' |
1650
|
|
|
); |
1651
|
|
|
} |
1652
|
|
|
|
1653
|
|
|
/** |
1654
|
|
|
* @group DDC-1236 |
1655
|
|
|
*/ |
1656
|
|
|
public function testGroupBySupportsIdentificationVariable() |
1657
|
|
|
{ |
1658
|
|
|
$this->assertSqlGeneration( |
1659
|
|
|
'SELECT u AS user FROM Doctrine\Tests\Models\CMS\CmsUser u GROUP BY user', |
1660
|
|
|
'SELECT c0_.id AS id_0, c0_.status AS status_1, c0_.username AS username_2, c0_.name AS name_3 FROM cms_users c0_ GROUP BY id_0, status_1, username_2, name_3' |
1661
|
|
|
); |
1662
|
|
|
} |
1663
|
|
|
|
1664
|
|
|
/** |
1665
|
|
|
* @group DDC-1213 |
1666
|
|
|
*/ |
1667
|
|
|
public function testSupportsBitComparison() |
1668
|
|
|
{ |
1669
|
|
|
$this->assertSqlGeneration( |
1670
|
|
|
'SELECT BIT_OR(4,2), BIT_AND(4,2), u FROM Doctrine\Tests\Models\CMS\CmsUser u', |
1671
|
|
|
'SELECT (4 | 2) AS sclr_0, (4 & 2) AS sclr_1, c0_.id AS id_2, c0_.status AS status_3, c0_.username AS username_4, c0_.name AS name_5 FROM cms_users c0_' |
1672
|
|
|
); |
1673
|
|
|
$this->assertSqlGeneration( |
1674
|
|
|
'SELECT BIT_OR(u.id,2), BIT_AND(u.id,2) FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE BIT_OR(u.id,2) > 0', |
1675
|
|
|
'SELECT (c0_.id | 2) AS sclr_0, (c0_.id & 2) AS sclr_1 FROM cms_users c0_ WHERE (c0_.id | 2) > 0' |
1676
|
|
|
); |
1677
|
|
|
$this->assertSqlGeneration( |
1678
|
|
|
'SELECT BIT_OR(u.id,2), BIT_AND(u.id,2) FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE BIT_AND(u.id , 4) > 0', |
1679
|
|
|
'SELECT (c0_.id | 2) AS sclr_0, (c0_.id & 2) AS sclr_1 FROM cms_users c0_ WHERE (c0_.id & 4) > 0' |
1680
|
|
|
); |
1681
|
|
|
$this->assertSqlGeneration( |
1682
|
|
|
'SELECT BIT_OR(u.id,2), BIT_AND(u.id,2) FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE BIT_OR(u.id , 2) > 0 OR BIT_AND(u.id , 4) > 0', |
1683
|
|
|
'SELECT (c0_.id | 2) AS sclr_0, (c0_.id & 2) AS sclr_1 FROM cms_users c0_ WHERE (c0_.id | 2) > 0 OR (c0_.id & 4) > 0' |
1684
|
|
|
); |
1685
|
|
|
} |
1686
|
|
|
|
1687
|
|
|
/** |
1688
|
|
|
* @group DDC-1539 |
1689
|
|
|
*/ |
1690
|
|
|
public function testParenthesesOnTheLeftHandOfComparison() |
1691
|
|
|
{ |
1692
|
|
|
$this->assertSqlGeneration( |
1693
|
|
|
'SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u where ( (u.id + u.id) * u.id ) > 100', |
1694
|
|
|
'SELECT c0_.id AS id_0, c0_.status AS status_1, c0_.username AS username_2, c0_.name AS name_3 FROM cms_users c0_ WHERE ((c0_.id + c0_.id) * c0_.id) > 100' |
1695
|
|
|
); |
1696
|
|
|
$this->assertSqlGeneration( |
1697
|
|
|
'SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u where (u.id + u.id) * u.id > 100', |
1698
|
|
|
'SELECT c0_.id AS id_0, c0_.status AS status_1, c0_.username AS username_2, c0_.name AS name_3 FROM cms_users c0_ WHERE (c0_.id + c0_.id) * c0_.id > 100' |
1699
|
|
|
); |
1700
|
|
|
$this->assertSqlGeneration( |
1701
|
|
|
'SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u where 100 < (u.id + u.id) * u.id ', |
1702
|
|
|
'SELECT c0_.id AS id_0, c0_.status AS status_1, c0_.username AS username_2, c0_.name AS name_3 FROM cms_users c0_ WHERE 100 < (c0_.id + c0_.id) * c0_.id' |
1703
|
|
|
); |
1704
|
|
|
} |
1705
|
|
|
|
1706
|
|
|
public function testSupportsParenthesisExpressionInSubSelect() { |
1707
|
|
|
$this->assertSqlGeneration( |
1708
|
|
|
'SELECT u.id, (SELECT (1000*SUM(subU.id)/SUM(subU.id)) FROM Doctrine\Tests\Models\CMS\CmsUser subU where subU.id = u.id) AS subSelect FROM Doctrine\Tests\Models\CMS\CmsUser u', |
1709
|
|
|
'SELECT c0_.id AS id_0, (SELECT (1000 * SUM(c1_.id) / SUM(c1_.id)) FROM cms_users c1_ WHERE c1_.id = c0_.id) AS sclr_1 FROM cms_users c0_' |
1710
|
|
|
); |
1711
|
|
|
} |
1712
|
|
|
|
1713
|
|
|
/** |
1714
|
|
|
* @group DDC-1557 |
1715
|
|
|
*/ |
1716
|
|
|
public function testSupportsSubSqlFunction() |
1717
|
|
|
{ |
1718
|
|
|
$this->assertSqlGeneration( |
1719
|
|
|
'SELECT u1 FROM Doctrine\Tests\Models\CMS\CmsUser u1 WHERE u1.name IN ( SELECT TRIM(u2.name) FROM Doctrine\Tests\Models\CMS\CmsUser u2 )', |
1720
|
|
|
'SELECT c0_.id AS id_0, c0_.status AS status_1, c0_.username AS username_2, c0_.name AS name_3 FROM cms_users c0_ WHERE c0_.name IN (SELECT TRIM(c1_.name) AS sclr_4 FROM cms_users c1_)' |
1721
|
|
|
); |
1722
|
|
|
$this->assertSqlGeneration( |
1723
|
|
|
'SELECT u1 FROM Doctrine\Tests\Models\CMS\CmsUser u1 WHERE u1.name IN ( SELECT TRIM(u2.name) FROM Doctrine\Tests\Models\CMS\CmsUser u2 WHERE LOWER(u2.name) LIKE \'%fabio%\')', |
1724
|
|
|
'SELECT c0_.id AS id_0, c0_.status AS status_1, c0_.username AS username_2, c0_.name AS name_3 FROM cms_users c0_ WHERE c0_.name IN (SELECT TRIM(c1_.name) AS sclr_4 FROM cms_users c1_ WHERE LOWER(c1_.name) LIKE \'%fabio%\')' |
1725
|
|
|
); |
1726
|
|
|
$this->assertSqlGeneration( |
1727
|
|
|
'SELECT u1 FROM Doctrine\Tests\Models\CMS\CmsUser u1 WHERE u1.email IN ( SELECT TRIM(IDENTITY(u2.email)) FROM Doctrine\Tests\Models\CMS\CmsUser u2 )', |
1728
|
|
|
'SELECT c0_.id AS id_0, c0_.status AS status_1, c0_.username AS username_2, c0_.name AS name_3 FROM cms_users c0_ WHERE c0_.email_id IN (SELECT TRIM(c1_.email_id) AS sclr_4 FROM cms_users c1_)' |
1729
|
|
|
); |
1730
|
|
|
$this->assertSqlGeneration( |
1731
|
|
|
'SELECT u1 FROM Doctrine\Tests\Models\CMS\CmsUser u1 WHERE u1.email IN ( SELECT IDENTITY(u2.email) FROM Doctrine\Tests\Models\CMS\CmsUser u2 )', |
1732
|
|
|
'SELECT c0_.id AS id_0, c0_.status AS status_1, c0_.username AS username_2, c0_.name AS name_3 FROM cms_users c0_ WHERE c0_.email_id IN (SELECT c1_.email_id AS sclr_4 FROM cms_users c1_)' |
1733
|
|
|
); |
1734
|
|
|
$this->assertSqlGeneration( |
1735
|
|
|
'SELECT u1 FROM Doctrine\Tests\Models\CMS\CmsUser u1 WHERE COUNT(u1.id) = ( SELECT SUM(u2.id) FROM Doctrine\Tests\Models\CMS\CmsUser u2 )', |
1736
|
|
|
'SELECT c0_.id AS id_0, c0_.status AS status_1, c0_.username AS username_2, c0_.name AS name_3 FROM cms_users c0_ WHERE COUNT(c0_.id) = (SELECT SUM(c1_.id) AS dctrn__1 FROM cms_users c1_)' |
1737
|
|
|
); |
1738
|
|
|
$this->assertSqlGeneration( |
1739
|
|
|
'SELECT u1 FROM Doctrine\Tests\Models\CMS\CmsUser u1 WHERE COUNT(u1.id) <= ( SELECT SUM(u2.id) + COUNT(u2.email) FROM Doctrine\Tests\Models\CMS\CmsUser u2 )', |
1740
|
|
|
'SELECT c0_.id AS id_0, c0_.status AS status_1, c0_.username AS username_2, c0_.name AS name_3 FROM cms_users c0_ WHERE COUNT(c0_.id) <= (SELECT SUM(c1_.id) + COUNT(c1_.email_id) AS sclr_4 FROM cms_users c1_)' |
1741
|
|
|
); |
1742
|
|
|
} |
1743
|
|
|
|
1744
|
|
|
/** |
1745
|
|
|
* @group DDC-1574 |
1746
|
|
|
*/ |
1747
|
|
|
public function testSupportsNewOperator() |
1748
|
|
|
{ |
1749
|
|
|
$this->assertSqlGeneration( |
1750
|
|
|
"SELECT new Doctrine\Tests\Models\CMS\CmsUserDTO(u.name, e.email, a.city) FROM Doctrine\Tests\Models\CMS\CmsUser u JOIN u.email e JOIN u.address a", |
1751
|
|
|
"SELECT c0_.name AS sclr_0, c1_.email AS sclr_1, c2_.city AS sclr_2 FROM cms_users c0_ INNER JOIN cms_emails c1_ ON c0_.email_id = c1_.id INNER JOIN cms_addresses c2_ ON c0_.id = c2_.user_id" |
1752
|
|
|
); |
1753
|
|
|
|
1754
|
|
|
$this->assertSqlGeneration( |
1755
|
|
|
"SELECT new Doctrine\Tests\Models\CMS\CmsUserDTO(u.name, e.email, a.id + u.id) FROM Doctrine\Tests\Models\CMS\CmsUser u JOIN u.email e JOIN u.address a", |
1756
|
|
|
"SELECT c0_.name AS sclr_0, c1_.email AS sclr_1, c2_.id + c0_.id AS sclr_2 FROM cms_users c0_ INNER JOIN cms_emails c1_ ON c0_.email_id = c1_.id INNER JOIN cms_addresses c2_ ON c0_.id = c2_.user_id" |
1757
|
|
|
); |
1758
|
|
|
|
1759
|
|
|
$this->assertSqlGeneration( |
1760
|
|
|
"SELECT new Doctrine\Tests\Models\CMS\CmsUserDTO(u.name, e.email, a.city, COUNT(p)) FROM Doctrine\Tests\Models\CMS\CmsUser u JOIN u.email e JOIN u.address a JOIN u.phonenumbers p", |
1761
|
|
|
"SELECT c0_.name AS sclr_0, c1_.email AS sclr_1, c2_.city AS sclr_2, COUNT(c3_.phonenumber) AS sclr_3 FROM cms_users c0_ INNER JOIN cms_emails c1_ ON c0_.email_id = c1_.id INNER JOIN cms_addresses c2_ ON c0_.id = c2_.user_id INNER JOIN cms_phonenumbers c3_ ON c0_.id = c3_.user_id" |
1762
|
|
|
); |
1763
|
|
|
|
1764
|
|
|
$this->assertSqlGeneration( |
1765
|
|
|
"SELECT new Doctrine\Tests\Models\CMS\CmsUserDTO(u.name, e.email, a.city, COUNT(p) + u.id) FROM Doctrine\Tests\Models\CMS\CmsUser u JOIN u.email e JOIN u.address a JOIN u.phonenumbers p", |
1766
|
|
|
"SELECT c0_.name AS sclr_0, c1_.email AS sclr_1, c2_.city AS sclr_2, COUNT(c3_.phonenumber) + c0_.id AS sclr_3 FROM cms_users c0_ INNER JOIN cms_emails c1_ ON c0_.email_id = c1_.id INNER JOIN cms_addresses c2_ ON c0_.id = c2_.user_id INNER JOIN cms_phonenumbers c3_ ON c0_.id = c3_.user_id" |
1767
|
|
|
); |
1768
|
|
|
|
1769
|
|
|
$this->assertSqlGeneration( |
1770
|
|
|
"SELECT new Doctrine\Tests\Models\CMS\CmsUserDTO(a.id, a.country, a.city), new Doctrine\Tests\Models\CMS\CmsAddressDTO(u.name, e.email) FROM Doctrine\Tests\Models\CMS\CmsUser u JOIN u.email e JOIN u.address a ORDER BY u.name", |
1771
|
|
|
"SELECT c0_.id AS sclr_0, c0_.country AS sclr_1, c0_.city AS sclr_2, c1_.name AS sclr_3, c2_.email AS sclr_4 FROM cms_users c1_ INNER JOIN cms_emails c2_ ON c1_.email_id = c2_.id INNER JOIN cms_addresses c0_ ON c1_.id = c0_.user_id ORDER BY c1_.name ASC" |
1772
|
|
|
); |
1773
|
|
|
|
1774
|
|
|
$this->assertSqlGeneration( |
1775
|
|
|
"SELECT new Doctrine\Tests\Models\CMS\CmsUserDTO(a.id, (SELECT 1 FROM Doctrine\Tests\Models\CMS\CmsUser su), a.country, a.city), new Doctrine\Tests\Models\CMS\CmsAddressDTO(u.name, e.email) FROM Doctrine\Tests\Models\CMS\CmsUser u JOIN u.email e JOIN u.address a ORDER BY u.name", |
1776
|
|
|
"SELECT c0_.id AS sclr_0, (SELECT 1 AS sclr_2 FROM cms_users c1_) AS sclr_1, c0_.country AS sclr_3, c0_.city AS sclr_4, c2_.name AS sclr_5, c3_.email AS sclr_6 FROM cms_users c2_ INNER JOIN cms_emails c3_ ON c2_.email_id = c3_.id INNER JOIN cms_addresses c0_ ON c2_.id = c0_.user_id ORDER BY c2_.name ASC" |
1777
|
|
|
); |
1778
|
|
|
} |
1779
|
|
|
|
1780
|
|
|
/** |
1781
|
|
|
* @group DDC-2234 |
1782
|
|
|
*/ |
1783
|
|
|
public function testWhereFunctionIsNullComparisonExpression() |
1784
|
|
|
{ |
1785
|
|
|
$this->assertSqlGeneration( |
1786
|
|
|
"SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE IDENTITY(u.email) IS NULL", |
1787
|
|
|
"SELECT c0_.id AS id_0, c0_.status AS status_1, c0_.username AS username_2, c0_.name AS name_3 FROM cms_users c0_ WHERE c0_.email_id IS NULL" |
1788
|
|
|
); |
1789
|
|
|
|
1790
|
|
|
$this->assertSqlGeneration( |
1791
|
|
|
"SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE NULLIF(u.name, 'FabioBatSilva') IS NULL AND IDENTITY(u.email) IS NOT NULL", |
1792
|
|
|
"SELECT c0_.id AS id_0, c0_.status AS status_1, c0_.username AS username_2, c0_.name AS name_3 FROM cms_users c0_ WHERE NULLIF(c0_.name, 'FabioBatSilva') IS NULL AND c0_.email_id IS NOT NULL" |
1793
|
|
|
); |
1794
|
|
|
|
1795
|
|
|
$this->assertSqlGeneration( |
1796
|
|
|
"SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE IDENTITY(u.email) IS NOT NULL", |
1797
|
|
|
"SELECT c0_.id AS id_0, c0_.status AS status_1, c0_.username AS username_2, c0_.name AS name_3 FROM cms_users c0_ WHERE c0_.email_id IS NOT NULL" |
1798
|
|
|
); |
1799
|
|
|
|
1800
|
|
|
$this->assertSqlGeneration( |
1801
|
|
|
"SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE NULLIF(u.name, 'FabioBatSilva') IS NOT NULL", |
1802
|
|
|
"SELECT c0_.id AS id_0, c0_.status AS status_1, c0_.username AS username_2, c0_.name AS name_3 FROM cms_users c0_ WHERE NULLIF(c0_.name, 'FabioBatSilva') IS NOT NULL" |
1803
|
|
|
); |
1804
|
|
|
|
1805
|
|
|
$this->assertSqlGeneration( |
1806
|
|
|
"SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE COALESCE(u.name, u.id) IS NOT NULL", |
1807
|
|
|
"SELECT c0_.id AS id_0, c0_.status AS status_1, c0_.username AS username_2, c0_.name AS name_3 FROM cms_users c0_ WHERE COALESCE(c0_.name, c0_.id) IS NOT NULL" |
1808
|
|
|
); |
1809
|
|
|
|
1810
|
|
|
$this->assertSqlGeneration( |
1811
|
|
|
"SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE COALESCE(u.id, IDENTITY(u.email)) IS NOT NULL", |
1812
|
|
|
"SELECT c0_.id AS id_0, c0_.status AS status_1, c0_.username AS username_2, c0_.name AS name_3 FROM cms_users c0_ WHERE COALESCE(c0_.id, c0_.email_id) IS NOT NULL" |
1813
|
|
|
); |
1814
|
|
|
|
1815
|
|
|
$this->assertSqlGeneration( |
1816
|
|
|
"SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE COALESCE(IDENTITY(u.email), NULLIF(u.name, 'FabioBatSilva')) IS NOT NULL", |
1817
|
|
|
"SELECT c0_.id AS id_0, c0_.status AS status_1, c0_.username AS username_2, c0_.name AS name_3 FROM cms_users c0_ WHERE COALESCE(c0_.email_id, NULLIF(c0_.name, 'FabioBatSilva')) IS NOT NULL" |
1818
|
|
|
); |
1819
|
|
|
} |
1820
|
|
|
|
1821
|
|
|
public function testCustomTypeValueSql() |
1822
|
|
|
{ |
1823
|
|
|
if (DBALType::hasType('negative_to_positive')) { |
1824
|
|
|
DBALType::overrideType('negative_to_positive', 'Doctrine\Tests\DbalTypes\NegativeToPositiveType'); |
1825
|
|
|
} else { |
1826
|
|
|
DBALType::addType('negative_to_positive', 'Doctrine\Tests\DbalTypes\NegativeToPositiveType'); |
1827
|
|
|
} |
1828
|
|
|
|
1829
|
|
|
$this->assertSqlGeneration( |
1830
|
|
|
'SELECT p.customInteger FROM Doctrine\Tests\Models\CustomType\CustomTypeParent p WHERE p.id = 1', |
1831
|
|
|
'SELECT -(c0_.customInteger) AS customInteger_0 FROM customtype_parents c0_ WHERE c0_.id = 1' |
1832
|
|
|
); |
1833
|
|
|
} |
1834
|
|
|
|
1835
|
|
|
public function testCustomTypeValueSqlIgnoresIdentifierColumn() |
1836
|
|
|
{ |
1837
|
|
|
if (DBALType::hasType('negative_to_positive')) { |
1838
|
|
|
DBALType::overrideType('negative_to_positive', 'Doctrine\Tests\DbalTypes\NegativeToPositiveType'); |
1839
|
|
|
} else { |
1840
|
|
|
DBALType::addType('negative_to_positive', 'Doctrine\Tests\DbalTypes\NegativeToPositiveType'); |
1841
|
|
|
} |
1842
|
|
|
|
1843
|
|
|
$this->assertSqlGeneration( |
1844
|
|
|
'SELECT p.id FROM Doctrine\Tests\Models\CustomType\CustomTypeParent p WHERE p.id = 1', |
1845
|
|
|
'SELECT c0_.id AS id_0 FROM customtype_parents c0_ WHERE c0_.id = 1' |
1846
|
|
|
); |
1847
|
|
|
} |
1848
|
|
|
|
1849
|
|
|
public function testCustomTypeValueSqlForAllFields() |
1850
|
|
|
{ |
1851
|
|
|
if (DBALType::hasType('negative_to_positive')) { |
1852
|
|
|
DBALType::overrideType('negative_to_positive', 'Doctrine\Tests\DbalTypes\NegativeToPositiveType'); |
1853
|
|
|
} else { |
1854
|
|
|
DBALType::addType('negative_to_positive', 'Doctrine\Tests\DbalTypes\NegativeToPositiveType'); |
1855
|
|
|
} |
1856
|
|
|
|
1857
|
|
|
$this->assertSqlGeneration( |
1858
|
|
|
'SELECT p FROM Doctrine\Tests\Models\CustomType\CustomTypeParent p', |
1859
|
|
|
'SELECT c0_.id AS id_0, -(c0_.customInteger) AS customInteger_1 FROM customtype_parents c0_' |
1860
|
|
|
); |
1861
|
|
|
} |
1862
|
|
|
|
1863
|
|
|
public function testCustomTypeValueSqlForPartialObject() |
1864
|
|
|
{ |
1865
|
|
|
if (DBALType::hasType('negative_to_positive')) { |
1866
|
|
|
DBALType::overrideType('negative_to_positive', 'Doctrine\Tests\DbalTypes\NegativeToPositiveType'); |
1867
|
|
|
} else { |
1868
|
|
|
DBALType::addType('negative_to_positive', 'Doctrine\Tests\DbalTypes\NegativeToPositiveType'); |
1869
|
|
|
} |
1870
|
|
|
|
1871
|
|
|
$this->assertSqlGeneration( |
1872
|
|
|
'SELECT partial p.{id, customInteger} FROM Doctrine\Tests\Models\CustomType\CustomTypeParent p', |
1873
|
|
|
'SELECT c0_.id AS id_0, -(c0_.customInteger) AS customInteger_1 FROM customtype_parents c0_' |
1874
|
|
|
); |
1875
|
|
|
} |
1876
|
|
|
|
1877
|
|
|
/** |
1878
|
|
|
* @group DDC-1529 |
1879
|
|
|
*/ |
1880
|
|
|
public function testMultipleFromAndInheritanceCondition() |
1881
|
|
|
{ |
1882
|
|
|
$this->assertSqlGeneration( |
1883
|
|
|
'SELECT fix, flex FROM Doctrine\Tests\Models\Company\CompanyFixContract fix, Doctrine\Tests\Models\Company\CompanyFlexContract flex', |
1884
|
|
|
"SELECT c0_.id AS id_0, c0_.completed AS completed_1, c0_.fixPrice AS fixPrice_2, c1_.id AS id_3, c1_.completed AS completed_4, c1_.hoursWorked AS hoursWorked_5, c1_.pricePerHour AS pricePerHour_6, c1_.maxPrice AS maxPrice_7, c0_.discr AS discr_8, c1_.discr AS discr_9 FROM company_contracts c0_, company_contracts c1_ WHERE (c0_.discr IN ('fix') AND c1_.discr IN ('flexible', 'flexultra'))" |
1885
|
|
|
); |
1886
|
|
|
} |
1887
|
|
|
|
1888
|
|
|
/** |
1889
|
|
|
* @group DDC-775 |
1890
|
|
|
*/ |
1891
|
|
|
public function testOrderByClauseSupportsSimpleArithmeticExpression() |
1892
|
|
|
{ |
1893
|
|
|
$this->assertSqlGeneration( |
1894
|
|
|
'SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u ORDER BY u.id + 1 ', |
1895
|
|
|
'SELECT c0_.id AS id_0, c0_.status AS status_1, c0_.username AS username_2, c0_.name AS name_3 FROM cms_users c0_ ORDER BY c0_.id + 1 ASC' |
1896
|
|
|
); |
1897
|
|
|
$this->assertSqlGeneration( |
1898
|
|
|
'SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u ORDER BY ( ( (u.id + 1) * (u.id - 1) ) / 2)', |
1899
|
|
|
'SELECT c0_.id AS id_0, c0_.status AS status_1, c0_.username AS username_2, c0_.name AS name_3 FROM cms_users c0_ ORDER BY (((c0_.id + 1) * (c0_.id - 1)) / 2) ASC' |
1900
|
|
|
); |
1901
|
|
|
$this->assertSqlGeneration( |
1902
|
|
|
'SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u ORDER BY ((u.id + 5000) * u.id + 3) ', |
1903
|
|
|
'SELECT c0_.id AS id_0, c0_.status AS status_1, c0_.username AS username_2, c0_.name AS name_3 FROM cms_users c0_ ORDER BY ((c0_.id + 5000) * c0_.id + 3) ASC' |
1904
|
|
|
); |
1905
|
|
|
} |
1906
|
|
|
|
1907
|
|
|
public function testOrderByClauseSupportsFunction() |
1908
|
|
|
{ |
1909
|
|
|
$this->assertSqlGeneration( |
1910
|
|
|
'SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u ORDER BY CONCAT(u.username, u.name) ', |
1911
|
|
|
'SELECT c0_.id AS id_0, c0_.status AS status_1, c0_.username AS username_2, c0_.name AS name_3 FROM cms_users c0_ ORDER BY c0_.username || c0_.name ASC' |
1912
|
|
|
); |
1913
|
|
|
} |
1914
|
|
|
|
1915
|
|
|
/** |
1916
|
|
|
* @group DDC-1719 |
1917
|
|
|
*/ |
1918
|
|
|
public function testStripNonAlphanumericCharactersFromAlias() |
1919
|
|
|
{ |
1920
|
|
|
$this->assertSqlGeneration( |
1921
|
|
|
'SELECT e FROM Doctrine\Tests\Models\Generic\NonAlphaColumnsEntity e', |
1922
|
|
|
'SELECT n0_."simple-entity-id" AS simpleentityid_0, n0_."simple-entity-value" AS simpleentityvalue_1 FROM "not-a-simple-entity" n0_' |
1923
|
|
|
); |
1924
|
|
|
|
1925
|
|
|
$this->assertSqlGeneration( |
1926
|
|
|
'SELECT e.value FROM Doctrine\Tests\Models\Generic\NonAlphaColumnsEntity e ORDER BY e.value', |
1927
|
|
|
'SELECT n0_."simple-entity-value" AS simpleentityvalue_0 FROM "not-a-simple-entity" n0_ ORDER BY n0_."simple-entity-value" ASC' |
1928
|
|
|
); |
1929
|
|
|
|
1930
|
|
|
$this->assertSqlGeneration( |
1931
|
|
|
'SELECT TRIM(e.value) FROM Doctrine\Tests\Models\Generic\NonAlphaColumnsEntity e ORDER BY e.value', |
1932
|
|
|
'SELECT TRIM(n0_."simple-entity-value") AS sclr_0 FROM "not-a-simple-entity" n0_ ORDER BY n0_."simple-entity-value" ASC' |
1933
|
|
|
); |
1934
|
|
|
} |
1935
|
|
|
|
1936
|
|
|
/** |
1937
|
|
|
* @group DDC-2435 |
1938
|
|
|
*/ |
1939
|
|
|
public function testColumnNameWithNumbersAndNonAlphanumericCharacters() |
1940
|
|
|
{ |
1941
|
|
|
$this->assertSqlGeneration( |
1942
|
|
|
'SELECT e FROM Doctrine\Tests\Models\Quote\NumericEntity e', |
1943
|
|
|
'SELECT t0_."1:1" AS 11_0, t0_."2:2" AS 22_1 FROM table t0_' |
1944
|
|
|
); |
1945
|
|
|
|
1946
|
|
|
$this->assertSqlGeneration( |
1947
|
|
|
'SELECT e.value FROM Doctrine\Tests\Models\Quote\NumericEntity e', |
1948
|
|
|
'SELECT t0_."2:2" AS 22_0 FROM table t0_' |
1949
|
|
|
); |
1950
|
|
|
|
1951
|
|
|
$this->assertSqlGeneration( |
1952
|
|
|
'SELECT TRIM(e.value) FROM Doctrine\Tests\Models\Quote\NumericEntity e', |
1953
|
|
|
'SELECT TRIM(t0_."2:2") AS sclr_0 FROM table t0_' |
1954
|
|
|
); |
1955
|
|
|
} |
1956
|
|
|
|
1957
|
|
|
/** |
1958
|
|
|
* @group DDC-1845 |
1959
|
|
|
*/ |
1960
|
|
|
public function testQuotedTableDeclaration() |
1961
|
|
|
{ |
1962
|
|
|
$this->assertSqlGeneration( |
1963
|
|
|
'SELECT u FROM Doctrine\Tests\Models\Quote\User u', |
1964
|
|
|
'SELECT q0_."user-id" AS userid_0, q0_."user-name" AS username_1 FROM "quote-user" q0_' |
1965
|
|
|
); |
1966
|
|
|
} |
1967
|
|
|
|
1968
|
|
|
/** |
1969
|
|
|
* @group DDC-1845 |
1970
|
|
|
*/ |
1971
|
|
|
public function testQuotedWalkJoinVariableDeclaration() |
1972
|
|
|
{ |
1973
|
|
|
$this->assertSqlGeneration( |
1974
|
|
|
'SELECT u, a FROM Doctrine\Tests\Models\Quote\User u JOIN u.address a', |
1975
|
|
|
'SELECT q0_."user-id" AS userid_0, q0_."user-name" AS username_1, q1_."address-id" AS addressid_2, q1_."address-zip" AS addresszip_3 FROM "quote-user" q0_ INNER JOIN "quote-address" q1_ ON q0_."address-id" = q1_."address-id"' |
1976
|
|
|
); |
1977
|
|
|
|
1978
|
|
|
$this->assertSqlGeneration( |
1979
|
|
|
'SELECT u, p FROM Doctrine\Tests\Models\Quote\User u JOIN u.phones p', |
1980
|
|
|
'SELECT q0_."user-id" AS userid_0, q0_."user-name" AS username_1, q1_."phone-number" AS phonenumber_2 FROM "quote-user" q0_ INNER JOIN "quote-phone" q1_ ON q0_."user-id" = q1_."user-id"' |
1981
|
|
|
); |
1982
|
|
|
|
1983
|
|
|
$this->assertSqlGeneration( |
1984
|
|
|
'SELECT u, g FROM Doctrine\Tests\Models\Quote\User u JOIN u.groups g', |
1985
|
|
|
'SELECT q0_."user-id" AS userid_0, q0_."user-name" AS username_1, q1_."group-id" AS groupid_2, q1_."group-name" AS groupname_3 FROM "quote-user" q0_ INNER JOIN "quote-users-groups" q2_ ON q0_."user-id" = q2_."user-id" INNER JOIN "quote-group" q1_ ON q1_."group-id" = q2_."group-id"' |
1986
|
|
|
); |
1987
|
|
|
|
1988
|
|
|
$this->assertSqlGeneration( |
1989
|
|
|
'SELECT a, u FROM Doctrine\Tests\Models\Quote\Address a JOIN a.user u', |
1990
|
|
|
'SELECT q0_."address-id" AS addressid_0, q0_."address-zip" AS addresszip_1, q1_."user-id" AS userid_2, q1_."user-name" AS username_3 FROM "quote-address" q0_ INNER JOIN "quote-user" q1_ ON q0_."user-id" = q1_."user-id"' |
1991
|
|
|
); |
1992
|
|
|
|
1993
|
|
|
$this->assertSqlGeneration( |
1994
|
|
|
'SELECT g, u FROM Doctrine\Tests\Models\Quote\Group g JOIN g.users u', |
1995
|
|
|
'SELECT q0_."group-id" AS groupid_0, q0_."group-name" AS groupname_1, q1_."user-id" AS userid_2, q1_."user-name" AS username_3 FROM "quote-group" q0_ INNER JOIN "quote-users-groups" q2_ ON q0_."group-id" = q2_."group-id" INNER JOIN "quote-user" q1_ ON q1_."user-id" = q2_."user-id"' |
1996
|
|
|
); |
1997
|
|
|
|
1998
|
|
|
$this->assertSqlGeneration( |
1999
|
|
|
'SELECT g, p FROM Doctrine\Tests\Models\Quote\Group g JOIN g.parent p', |
2000
|
|
|
'SELECT q0_."group-id" AS groupid_0, q0_."group-name" AS groupname_1, q1_."group-id" AS groupid_2, q1_."group-name" AS groupname_3 FROM "quote-group" q0_ INNER JOIN "quote-group" q1_ ON q0_."parent-id" = q1_."group-id"' |
2001
|
|
|
); |
2002
|
|
|
} |
2003
|
|
|
|
2004
|
|
|
/** |
2005
|
|
|
* @group DDC-2208 |
2006
|
|
|
*/ |
2007
|
|
|
public function testCaseThenParameterArithmeticExpression() |
2008
|
|
|
{ |
2009
|
|
|
$this->assertSqlGeneration( |
2010
|
|
|
'SELECT SUM(CASE WHEN e.salary <= :value THEN e.salary - :value WHEN e.salary >= :value THEN :value - e.salary ELSE 0 END) FROM Doctrine\Tests\Models\Company\CompanyEmployee e', |
2011
|
|
|
'SELECT SUM(CASE WHEN c0_.salary <= ? THEN c0_.salary - ? WHEN c0_.salary >= ? THEN ? - c0_.salary ELSE 0 END) AS sclr_0 FROM company_employees c0_ INNER JOIN company_persons c1_ ON c0_.id = c1_.id' |
2012
|
|
|
); |
2013
|
|
|
|
2014
|
|
|
$this->assertSqlGeneration( |
2015
|
|
|
'SELECT SUM(CASE WHEN e.salary <= :value THEN e.salary - :value WHEN e.salary >= :value THEN :value - e.salary ELSE e.salary + 0 END) FROM Doctrine\Tests\Models\Company\CompanyEmployee e', |
2016
|
|
|
'SELECT SUM(CASE WHEN c0_.salary <= ? THEN c0_.salary - ? WHEN c0_.salary >= ? THEN ? - c0_.salary ELSE c0_.salary + 0 END) AS sclr_0 FROM company_employees c0_ INNER JOIN company_persons c1_ ON c0_.id = c1_.id' |
2017
|
|
|
); |
2018
|
|
|
|
2019
|
|
|
$this->assertSqlGeneration( |
2020
|
|
|
'SELECT SUM(CASE WHEN e.salary <= :value THEN (e.salary - :value) WHEN e.salary >= :value THEN (:value - e.salary) ELSE (e.salary + :value) END) FROM Doctrine\Tests\Models\Company\CompanyEmployee e', |
2021
|
|
|
'SELECT SUM(CASE WHEN c0_.salary <= ? THEN (c0_.salary - ?) WHEN c0_.salary >= ? THEN (? - c0_.salary) ELSE (c0_.salary + ?) END) AS sclr_0 FROM company_employees c0_ INNER JOIN company_persons c1_ ON c0_.id = c1_.id' |
2022
|
|
|
); |
2023
|
|
|
} |
2024
|
|
|
|
2025
|
|
|
/** |
2026
|
|
|
* @group DDC-2268 |
2027
|
|
|
*/ |
2028
|
|
|
public function testCaseThenFunction() |
2029
|
|
|
{ |
2030
|
|
|
$this->assertSqlGeneration( |
2031
|
|
|
'SELECT CASE WHEN LENGTH(u.name) <> 0 THEN CONCAT(u.id, u.name) ELSE u.id END AS name FROM Doctrine\Tests\Models\CMS\CmsUser u', |
2032
|
|
|
'SELECT CASE WHEN LENGTH(c0_.name) <> 0 THEN c0_.id || c0_.name ELSE c0_.id END AS sclr_0 FROM cms_users c0_' |
2033
|
|
|
); |
2034
|
|
|
|
2035
|
|
|
$this->assertSqlGeneration( |
2036
|
|
|
'SELECT CASE WHEN LENGTH(u.name) <> LENGTH(TRIM(u.name)) THEN TRIM(u.name) ELSE u.name END AS name FROM Doctrine\Tests\Models\CMS\CmsUser u', |
2037
|
|
|
'SELECT CASE WHEN LENGTH(c0_.name) <> LENGTH(TRIM(c0_.name)) THEN TRIM(c0_.name) ELSE c0_.name END AS sclr_0 FROM cms_users c0_' |
2038
|
|
|
); |
2039
|
|
|
|
2040
|
|
|
$this->assertSqlGeneration( |
2041
|
|
|
'SELECT CASE WHEN LENGTH(u.name) > :value THEN SUBSTRING(u.name, 0, :value) ELSE TRIM(u.name) END AS name FROM Doctrine\Tests\Models\CMS\CmsUser u', |
2042
|
|
|
'SELECT CASE WHEN LENGTH(c0_.name) > ? THEN SUBSTRING(c0_.name FROM 0 FOR ?) ELSE TRIM(c0_.name) END AS sclr_0 FROM cms_users c0_' |
2043
|
|
|
); |
2044
|
|
|
} |
2045
|
|
|
|
2046
|
|
|
/** |
2047
|
|
|
* @group DDC-2268 |
2048
|
|
|
*/ |
2049
|
|
|
public function testSupportsMoreThanTwoParametersInConcatFunction() |
2050
|
|
|
{ |
2051
|
|
|
$connMock = $this->_em->getConnection(); |
2052
|
|
|
$orgPlatform = $connMock->getDatabasePlatform(); |
2053
|
|
|
|
2054
|
|
|
$connMock->setDatabasePlatform(new \Doctrine\DBAL\Platforms\MySqlPlatform); |
2055
|
|
|
$this->assertSqlGeneration( |
2056
|
|
|
"SELECT u.id FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE CONCAT(u.name, u.status, 's') = ?1", |
2057
|
|
|
"SELECT c0_.id AS id_0 FROM cms_users c0_ WHERE CONCAT(c0_.name, c0_.status, 's') = ?" |
2058
|
|
|
); |
2059
|
|
|
$this->assertSqlGeneration( |
2060
|
|
|
"SELECT CONCAT(u.id, u.name, u.status) FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE u.id = ?1", |
2061
|
|
|
"SELECT CONCAT(c0_.id, c0_.name, c0_.status) AS sclr_0 FROM cms_users c0_ WHERE c0_.id = ?" |
2062
|
|
|
); |
2063
|
|
|
|
2064
|
|
|
$connMock->setDatabasePlatform(new \Doctrine\DBAL\Platforms\PostgreSqlPlatform); |
2065
|
|
|
$this->assertSqlGeneration( |
2066
|
|
|
"SELECT u.id FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE CONCAT(u.name, u.status, 's') = ?1", |
2067
|
|
|
"SELECT c0_.id AS id_0 FROM cms_users c0_ WHERE c0_.name || c0_.status || 's' = ?" |
2068
|
|
|
); |
2069
|
|
|
$this->assertSqlGeneration( |
2070
|
|
|
"SELECT CONCAT(u.id, u.name, u.status) FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE u.id = ?1", |
2071
|
|
|
"SELECT c0_.id || c0_.name || c0_.status AS sclr_0 FROM cms_users c0_ WHERE c0_.id = ?" |
2072
|
|
|
); |
2073
|
|
|
|
2074
|
|
|
$connMock->setDatabasePlatform(new \Doctrine\DBAL\Platforms\SQLServerPlatform()); |
2075
|
|
|
$this->assertSqlGeneration( |
2076
|
|
|
"SELECT u.id FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE CONCAT(u.name, u.status, 's') = ?1", |
2077
|
|
|
"SELECT c0_.id AS id_0 FROM cms_users c0_ WHERE (c0_.name + c0_.status + 's') = ?" |
2078
|
|
|
); |
2079
|
|
|
$this->assertSqlGeneration( |
2080
|
|
|
"SELECT CONCAT(u.id, u.name, u.status) FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE u.id = ?1", |
2081
|
|
|
"SELECT (c0_.id + c0_.name + c0_.status) AS sclr_0 FROM cms_users c0_ WHERE c0_.id = ?" |
2082
|
|
|
); |
2083
|
|
|
|
2084
|
|
|
$connMock->setDatabasePlatform($orgPlatform); |
2085
|
|
|
} |
2086
|
|
|
|
2087
|
|
|
/** |
2088
|
|
|
* @group DDC-2188 |
2089
|
|
|
*/ |
2090
|
|
|
public function testArithmeticPriority() |
2091
|
|
|
{ |
2092
|
|
|
$this->assertSqlGeneration( |
2093
|
|
|
'SELECT 100/(2*2) FROM Doctrine\Tests\Models\CMS\CmsUser u', |
2094
|
|
|
'SELECT 100 / (2 * 2) AS sclr_0 FROM cms_users c0_' |
2095
|
|
|
); |
2096
|
|
|
|
2097
|
|
|
$this->assertSqlGeneration( |
2098
|
|
|
'SELECT (u.id / (u.id * 2)) FROM Doctrine\Tests\Models\CMS\CmsUser u', |
2099
|
|
|
'SELECT (c0_.id / (c0_.id * 2)) AS sclr_0 FROM cms_users c0_' |
2100
|
|
|
); |
2101
|
|
|
|
2102
|
|
|
$this->assertSqlGeneration( |
2103
|
|
|
'SELECT 100/(2*2) + (u.id / (u.id * 2)) FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE (u.id / (u.id * 2)) > 0', |
2104
|
|
|
'SELECT 100 / (2 * 2) + (c0_.id / (c0_.id * 2)) AS sclr_0 FROM cms_users c0_ WHERE (c0_.id / (c0_.id * 2)) > 0' |
2105
|
|
|
); |
2106
|
|
|
} |
2107
|
|
|
|
2108
|
|
|
/** |
2109
|
|
|
* @group DDC-2475 |
2110
|
|
|
*/ |
2111
|
|
|
public function testOrderByClauseShouldReplaceOrderByRelationMapping() |
2112
|
|
|
{ |
2113
|
|
|
$this->assertSqlGeneration( |
2114
|
|
|
'SELECT r, b FROM Doctrine\Tests\Models\Routing\RoutingRoute r JOIN r.bookings b', |
2115
|
|
|
'SELECT r0_.id AS id_0, r1_.id AS id_1, r1_.passengerName AS passengerName_2 FROM RoutingRoute r0_ INNER JOIN RoutingRouteBooking r1_ ON r0_.id = r1_.route_id ORDER BY r1_.passengerName ASC' |
2116
|
|
|
); |
2117
|
|
|
|
2118
|
|
|
$this->assertSqlGeneration( |
2119
|
|
|
'SELECT r, b FROM Doctrine\Tests\Models\Routing\RoutingRoute r JOIN r.bookings b ORDER BY b.passengerName DESC', |
2120
|
|
|
'SELECT r0_.id AS id_0, r1_.id AS id_1, r1_.passengerName AS passengerName_2 FROM RoutingRoute r0_ INNER JOIN RoutingRouteBooking r1_ ON r0_.id = r1_.route_id ORDER BY r1_.passengerName DESC' |
2121
|
|
|
); |
2122
|
|
|
} |
2123
|
|
|
|
2124
|
|
|
/** |
2125
|
|
|
* @group DDC-1858 |
2126
|
|
|
*/ |
2127
|
|
|
public function testHavingSupportIsNullExpression() |
2128
|
|
|
{ |
2129
|
|
|
$this->assertSqlGeneration( |
2130
|
|
|
'SELECT u.name FROM Doctrine\Tests\Models\CMS\CmsUser u HAVING u.username IS NULL', |
2131
|
|
|
'SELECT c0_.name AS name_0 FROM cms_users c0_ HAVING c0_.username IS NULL' |
2132
|
|
|
); |
2133
|
|
|
|
2134
|
|
|
$this->assertSqlGeneration( |
2135
|
|
|
'SELECT u.name FROM Doctrine\Tests\Models\CMS\CmsUser u HAVING MAX(u.name) IS NULL', |
2136
|
|
|
'SELECT c0_.name AS name_0 FROM cms_users c0_ HAVING MAX(c0_.name) IS NULL' |
2137
|
|
|
); |
2138
|
|
|
} |
2139
|
|
|
|
2140
|
|
|
/** |
2141
|
|
|
* @group DDC-2506 |
2142
|
|
|
*/ |
2143
|
|
|
public function testClassTableInheritanceJoinWithConditionAppliesToBaseTable() |
2144
|
|
|
{ |
2145
|
|
|
$this->assertSqlGeneration( |
2146
|
|
|
'SELECT e.id FROM Doctrine\Tests\Models\Company\CompanyOrganization o JOIN o.events e WITH e.id = ?1', |
2147
|
|
|
'SELECT c0_.id AS id_0 FROM company_organizations c1_ INNER JOIN (company_events c0_ LEFT JOIN company_auctions c2_ ON c0_.id = c2_.id LEFT JOIN company_raffles c3_ ON c0_.id = c3_.id) ON c1_.id = c0_.org_id AND (c0_.id = ?)', |
2148
|
|
|
array(Query::HINT_FORCE_PARTIAL_LOAD => false) |
2149
|
|
|
); |
2150
|
|
|
} |
2151
|
|
|
|
2152
|
|
|
/** |
2153
|
|
|
* @group DDC-2235 |
2154
|
|
|
*/ |
2155
|
|
|
public function testSingleTableInheritanceLeftJoinWithCondition() |
2156
|
|
|
{ |
2157
|
|
|
// Regression test for the bug |
2158
|
|
|
$this->assertSqlGeneration( |
2159
|
|
|
'SELECT c FROM Doctrine\Tests\Models\Company\CompanyEmployee e LEFT JOIN Doctrine\Tests\Models\Company\CompanyContract c WITH c.salesPerson = e.id', |
2160
|
|
|
"SELECT c0_.id AS id_0, c0_.completed AS completed_1, c0_.fixPrice AS fixPrice_2, c0_.hoursWorked AS hoursWorked_3, c0_.pricePerHour AS pricePerHour_4, c0_.maxPrice AS maxPrice_5, c0_.discr AS discr_6 FROM company_employees c1_ INNER JOIN company_persons c2_ ON c1_.id = c2_.id LEFT JOIN company_contracts c0_ ON (c0_.salesPerson_id = c2_.id) AND c0_.discr IN ('fix', 'flexible', 'flexultra')" |
2161
|
|
|
); |
2162
|
|
|
} |
2163
|
|
|
|
2164
|
|
|
/** |
2165
|
|
|
* @group DDC-2235 |
2166
|
|
|
*/ |
2167
|
|
|
public function testSingleTableInheritanceLeftJoinWithConditionAndWhere() |
2168
|
|
|
{ |
2169
|
|
|
// Ensure other WHERE predicates are passed through to the main WHERE clause |
2170
|
|
|
$this->assertSqlGeneration( |
2171
|
|
|
'SELECT c FROM Doctrine\Tests\Models\Company\CompanyEmployee e LEFT JOIN Doctrine\Tests\Models\Company\CompanyContract c WITH c.salesPerson = e.id WHERE e.salary > 1000', |
2172
|
|
|
"SELECT c0_.id AS id_0, c0_.completed AS completed_1, c0_.fixPrice AS fixPrice_2, c0_.hoursWorked AS hoursWorked_3, c0_.pricePerHour AS pricePerHour_4, c0_.maxPrice AS maxPrice_5, c0_.discr AS discr_6 FROM company_employees c1_ INNER JOIN company_persons c2_ ON c1_.id = c2_.id LEFT JOIN company_contracts c0_ ON (c0_.salesPerson_id = c2_.id) AND c0_.discr IN ('fix', 'flexible', 'flexultra') WHERE c1_.salary > 1000" |
2173
|
|
|
); |
2174
|
|
|
} |
2175
|
|
|
|
2176
|
|
|
/** |
2177
|
|
|
* @group DDC-2235 |
2178
|
|
|
*/ |
2179
|
|
|
public function testSingleTableInheritanceInnerJoinWithCondition() |
2180
|
|
|
{ |
2181
|
|
|
// Test inner joins too |
2182
|
|
|
$this->assertSqlGeneration( |
2183
|
|
|
'SELECT c FROM Doctrine\Tests\Models\Company\CompanyEmployee e INNER JOIN Doctrine\Tests\Models\Company\CompanyContract c WITH c.salesPerson = e.id', |
2184
|
|
|
"SELECT c0_.id AS id_0, c0_.completed AS completed_1, c0_.fixPrice AS fixPrice_2, c0_.hoursWorked AS hoursWorked_3, c0_.pricePerHour AS pricePerHour_4, c0_.maxPrice AS maxPrice_5, c0_.discr AS discr_6 FROM company_employees c1_ INNER JOIN company_persons c2_ ON c1_.id = c2_.id INNER JOIN company_contracts c0_ ON (c0_.salesPerson_id = c2_.id) AND c0_.discr IN ('fix', 'flexible', 'flexultra')" |
2185
|
|
|
); |
2186
|
|
|
} |
2187
|
|
|
|
2188
|
|
|
/** |
2189
|
|
|
* @group DDC-2235 |
2190
|
|
|
*/ |
2191
|
|
|
public function testSingleTableInheritanceLeftJoinNonAssociationWithConditionAndWhere() |
2192
|
|
|
{ |
2193
|
|
|
// Test that the discriminator IN() predicate is still added into |
2194
|
|
|
// the where clause when not joining onto that table |
2195
|
|
|
$this->assertSqlGeneration( |
2196
|
|
|
'SELECT c FROM Doctrine\Tests\Models\Company\CompanyContract c LEFT JOIN Doctrine\Tests\Models\Company\CompanyEmployee e WITH e.id = c.salesPerson WHERE c.completed = true', |
2197
|
|
|
"SELECT c0_.id AS id_0, c0_.completed AS completed_1, c0_.fixPrice AS fixPrice_2, c0_.hoursWorked AS hoursWorked_3, c0_.pricePerHour AS pricePerHour_4, c0_.maxPrice AS maxPrice_5, c0_.discr AS discr_6 FROM company_contracts c0_ LEFT JOIN company_employees c1_ INNER JOIN company_persons c2_ ON c1_.id = c2_.id ON (c2_.id = c0_.salesPerson_id) WHERE (c0_.completed = 1) AND c0_.discr IN ('fix', 'flexible', 'flexultra')" |
2198
|
|
|
); |
2199
|
|
|
} |
2200
|
|
|
|
2201
|
|
|
/** |
2202
|
|
|
* @group DDC-2235 |
2203
|
|
|
*/ |
2204
|
|
|
public function testSingleTableInheritanceJoinCreatesOnCondition() |
2205
|
|
|
{ |
2206
|
|
|
// Test that the discriminator IN() predicate is still added |
2207
|
|
|
// into the where clause when not joining onto a single table inheritance entity |
2208
|
|
|
// via a join association |
2209
|
|
|
$this->assertSqlGeneration( |
2210
|
|
|
'SELECT c FROM Doctrine\Tests\Models\Company\CompanyContract c JOIN c.salesPerson s WHERE c.completed = true', |
2211
|
|
|
"SELECT c0_.id AS id_0, c0_.completed AS completed_1, c0_.fixPrice AS fixPrice_2, c0_.hoursWorked AS hoursWorked_3, c0_.pricePerHour AS pricePerHour_4, c0_.maxPrice AS maxPrice_5, c0_.discr AS discr_6 FROM company_contracts c0_ INNER JOIN company_employees c1_ ON c0_.salesPerson_id = c1_.id LEFT JOIN company_persons c2_ ON c1_.id = c2_.id WHERE (c0_.completed = 1) AND c0_.discr IN ('fix', 'flexible', 'flexultra')" |
2212
|
|
|
); |
2213
|
|
|
} |
2214
|
|
|
|
2215
|
|
|
/** |
2216
|
|
|
* @group DDC-2235 |
2217
|
|
|
*/ |
2218
|
|
|
public function testSingleTableInheritanceCreatesOnConditionAndWhere() |
2219
|
|
|
{ |
2220
|
|
|
// Test that when joining onto an entity using single table inheritance via |
2221
|
|
|
// a join association that the discriminator IN() predicate is placed |
2222
|
|
|
// into the ON clause of the join |
2223
|
|
|
$this->assertSqlGeneration( |
2224
|
|
|
'SELECT e, COUNT(c) FROM Doctrine\Tests\Models\Company\CompanyEmployee e JOIN e.contracts c WHERE e.department = :department', |
2225
|
|
|
"SELECT c0_.id AS id_0, c0_.name AS name_1, c1_.salary AS salary_2, c1_.department AS department_3, c1_.startDate AS startDate_4, COUNT(c2_.id) AS sclr_5, c0_.discr AS discr_6 FROM company_employees c1_ INNER JOIN company_persons c0_ ON c1_.id = c0_.id INNER JOIN company_contract_employees c3_ ON c1_.id = c3_.employee_id INNER JOIN company_contracts c2_ ON c2_.id = c3_.contract_id AND c2_.discr IN ('fix', 'flexible', 'flexultra') WHERE c1_.department = ?", |
2226
|
|
|
array(), |
2227
|
|
|
array('department' => 'foobar') |
2228
|
|
|
); |
2229
|
|
|
} |
2230
|
|
|
|
2231
|
|
|
/** |
2232
|
|
|
* @group DDC-1858 |
2233
|
|
|
*/ |
2234
|
|
|
public function testHavingSupportResultVariableInExpression() |
2235
|
|
|
{ |
2236
|
|
|
$this->assertSqlGeneration( |
2237
|
|
|
'SELECT u.name AS foo FROM Doctrine\Tests\Models\CMS\CmsUser u HAVING foo IN (?1)', |
2238
|
|
|
'SELECT c0_.name AS name_0 FROM cms_users c0_ HAVING name_0 IN (?)' |
2239
|
|
|
); |
2240
|
|
|
} |
2241
|
|
|
|
2242
|
|
|
/** |
2243
|
|
|
* @group DDC-1858 |
2244
|
|
|
*/ |
2245
|
|
|
public function testHavingSupportResultVariableLikeExpression() |
2246
|
|
|
{ |
2247
|
|
|
$this->assertSqlGeneration( |
2248
|
|
|
"SELECT u.name AS foo FROM Doctrine\Tests\Models\CMS\CmsUser u HAVING foo LIKE '3'", |
2249
|
|
|
"SELECT c0_.name AS name_0 FROM cms_users c0_ HAVING name_0 LIKE '3'" |
2250
|
|
|
); |
2251
|
|
|
} |
2252
|
|
|
|
2253
|
|
|
/** |
2254
|
|
|
* @group DDC-3085 |
2255
|
|
|
*/ |
2256
|
|
|
public function testHavingSupportResultVariableNullComparisonExpression() |
2257
|
|
|
{ |
2258
|
|
|
$this->assertSqlGeneration( |
2259
|
|
|
"SELECT u AS user, SUM(a.id) AS score FROM Doctrine\Tests\Models\CMS\CmsUser u LEFT JOIN Doctrine\Tests\Models\CMS\CmsAddress a WITH a.user = u GROUP BY u HAVING score IS NOT NULL AND score >= 5", |
2260
|
|
|
"SELECT c0_.id AS id_0, c0_.status AS status_1, c0_.username AS username_2, c0_.name AS name_3, SUM(c1_.id) AS sclr_4 FROM cms_users c0_ LEFT JOIN cms_addresses c1_ ON (c1_.user_id = c0_.id) GROUP BY c0_.id, c0_.status, c0_.username, c0_.name, c0_.email_id HAVING sclr_4 IS NOT NULL AND sclr_4 >= 5" |
2261
|
|
|
); |
2262
|
|
|
} |
2263
|
|
|
|
2264
|
|
|
/** |
2265
|
|
|
* @group DDC-1858 |
2266
|
|
|
*/ |
2267
|
|
|
public function testHavingSupportResultVariableInAggregateFunction() |
2268
|
|
|
{ |
2269
|
|
|
$this->assertSqlGeneration( |
2270
|
|
|
'SELECT COUNT(u.name) AS countName FROM Doctrine\Tests\Models\CMS\CmsUser u HAVING countName IS NULL', |
2271
|
|
|
'SELECT COUNT(c0_.name) AS sclr_0 FROM cms_users c0_ HAVING sclr_0 IS NULL' |
2272
|
|
|
); |
2273
|
|
|
} |
2274
|
|
|
|
2275
|
|
|
/** |
2276
|
|
|
* GitHub issue #4764: https://github.com/doctrine/doctrine2/issues/4764 |
2277
|
|
|
* @group DDC-3907 |
2278
|
|
|
* @dataProvider mathematicOperatorsProvider |
2279
|
|
|
*/ |
2280
|
|
|
public function testHavingRegressionUsingVariableWithMathOperatorsExpression($operator) |
2281
|
|
|
{ |
2282
|
|
|
$this->assertSqlGeneration( |
2283
|
|
|
'SELECT COUNT(u.name) AS countName FROM Doctrine\Tests\Models\CMS\CmsUser u HAVING 1 ' . $operator . ' countName > 0', |
2284
|
|
|
'SELECT COUNT(c0_.name) AS sclr_0 FROM cms_users c0_ HAVING 1 ' . $operator . ' sclr_0 > 0' |
2285
|
|
|
); |
2286
|
|
|
} |
2287
|
|
|
|
2288
|
|
|
/** |
2289
|
|
|
* @return array |
2290
|
|
|
*/ |
2291
|
|
|
public function mathematicOperatorsProvider() |
2292
|
|
|
{ |
2293
|
|
|
return [['+'], ['-'], ['*'], ['/']]; |
2294
|
|
|
} |
2295
|
|
|
} |
2296
|
|
|
|
2297
|
|
|
class MyAbsFunction extends \Doctrine\ORM\Query\AST\Functions\FunctionNode |
2298
|
|
|
{ |
2299
|
|
|
public $simpleArithmeticExpression; |
2300
|
|
|
|
2301
|
|
|
/** |
2302
|
|
|
* @override |
2303
|
|
|
*/ |
2304
|
|
|
public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker) |
2305
|
|
|
{ |
2306
|
|
|
return 'ABS(' . $sqlWalker->walkSimpleArithmeticExpression($this->simpleArithmeticExpression) . ')'; |
|
|
|
|
2307
|
|
|
} |
2308
|
|
|
|
2309
|
|
|
/** |
2310
|
|
|
* @override |
2311
|
|
|
*/ |
2312
|
|
|
public function parse(\Doctrine\ORM\Query\Parser $parser) |
2313
|
|
|
{ |
2314
|
|
|
$lexer = $parser->getLexer(); |
|
|
|
|
2315
|
|
|
|
2316
|
|
|
$parser->match(\Doctrine\ORM\Query\Lexer::T_IDENTIFIER); |
2317
|
|
|
$parser->match(\Doctrine\ORM\Query\Lexer::T_OPEN_PARENTHESIS); |
2318
|
|
|
|
2319
|
|
|
$this->simpleArithmeticExpression = $parser->SimpleArithmeticExpression(); |
2320
|
|
|
|
2321
|
|
|
$parser->match(\Doctrine\ORM\Query\Lexer::T_CLOSE_PARENTHESIS); |
2322
|
|
|
} |
2323
|
|
|
} |
2324
|
|
|
/** |
2325
|
|
|
* @Entity |
2326
|
|
|
*/ |
2327
|
|
|
class DDC1384Model |
2328
|
|
|
{ |
2329
|
|
|
/** |
2330
|
|
|
* @Id |
2331
|
|
|
* @Column(type="integer") |
2332
|
|
|
* @GeneratedValue |
2333
|
|
|
*/ |
2334
|
|
|
protected $aVeryLongIdentifierThatShouldBeShortenedByTheSQLWalker_fooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo; |
2335
|
|
|
} |
2336
|
|
|
|
2337
|
|
|
|
2338
|
|
|
/** |
2339
|
|
|
* @Entity |
2340
|
|
|
*/ |
2341
|
|
|
class DDC1474Entity |
2342
|
|
|
{ |
2343
|
|
|
|
2344
|
|
|
/** |
2345
|
|
|
* @Id |
2346
|
|
|
* @Column(type="integer") |
2347
|
|
|
* @GeneratedValue() |
2348
|
|
|
*/ |
2349
|
|
|
protected $id; |
2350
|
|
|
|
2351
|
|
|
/** |
2352
|
|
|
* @column(type="float") |
2353
|
|
|
*/ |
2354
|
|
|
private $value; |
2355
|
|
|
|
2356
|
|
|
/** |
2357
|
|
|
* @param string $float |
2358
|
|
|
*/ |
2359
|
|
|
public function __construct($float) |
2360
|
|
|
{ |
2361
|
|
|
$this->value = $float; |
2362
|
|
|
} |
2363
|
|
|
|
2364
|
|
|
/** |
2365
|
|
|
* @return int |
2366
|
|
|
*/ |
2367
|
|
|
public function getId() |
2368
|
|
|
{ |
2369
|
|
|
return $this->id; |
2370
|
|
|
} |
2371
|
|
|
|
2372
|
|
|
/** |
2373
|
|
|
* @return float |
2374
|
|
|
*/ |
2375
|
|
|
public function getValue() |
2376
|
|
|
{ |
2377
|
|
|
return $this->value; |
2378
|
|
|
} |
2379
|
|
|
|
2380
|
|
|
/** |
2381
|
|
|
* @param float $value |
2382
|
|
|
*/ |
2383
|
|
|
public function setValue($value) |
2384
|
|
|
{ |
2385
|
|
|
$this->value = $value; |
2386
|
|
|
} |
2387
|
|
|
|
2388
|
|
|
} |
2389
|
|
|
|
This check looks for a call to a parent method whose name is different than the method from which it is called.
Consider the following code:
The
getFirstName()
method in theSon
calls the wrong method in the parent class.