QueryBuilderTest.php 18.8 KB
Newer Older
1 2 3 4 5 6 7 8 9
<?php

namespace Doctrine\Tests\DBAL\Query;

use Doctrine\DBAL\Query\Expression\ExpressionBuilder,
    Doctrine\DBAL\Query\QueryBuilder;

require_once __DIR__ . '/../../TestInit.php';

10 11 12
/**
 * @group DBAL-12
 */
13 14 15
class QueryBuilderTest extends \Doctrine\Tests\DbalTestCase
{
    protected $conn;
16

17 18 19
    public function setUp()
    {
        $this->conn = $this->getMock('Doctrine\DBAL\Connection', array(), array(), '', false);
20

21
        $expressionBuilder = new ExpressionBuilder($this->conn);
22

23 24 25 26
        $this->conn->expects($this->any())
                   ->method('getExpressionBuilder')
                   ->will($this->returnValue($expressionBuilder));
    }
27

28 29 30
    public function testSimpleSelect()
    {
        $qb = new QueryBuilder($this->conn);
31

32 33
        $qb->select('u.id')
           ->from('users', 'u');
34

35
        $this->assertEquals('SELECT u.id FROM users u', (string) $qb);
36
    }
37

38 39 40
    public function testSelectWithSimpleWhere()
    {
        $qb   = new QueryBuilder($this->conn);
41 42
        $expr = $qb->expr();

43 44 45
        $qb->select('u.id')
           ->from('users', 'u')
           ->where($expr->andX($expr->eq('u.nickname', '?')));
46

47
        $this->assertEquals("SELECT u.id FROM users u WHERE u.nickname = ?", (string) $qb);
48
    }
49

50
    public function testSelectWithLeftJoin()
51 52
    {
        $qb   = new QueryBuilder($this->conn);
53 54
        $expr = $qb->expr();

55 56 57
        $qb->select('u.*', 'p.*')
           ->from('users', 'u')
           ->leftJoin('u', 'phones', 'p', $expr->eq('p.user_id', 'u.id'));
58

59
        $this->assertEquals('SELECT u.*, p.* FROM users u LEFT JOIN phones p ON p.user_id = u.id', (string) $qb);
60
    }
61

62 63 64
    public function testSelectWithJoin()
    {
        $qb   = new QueryBuilder($this->conn);
65 66
        $expr = $qb->expr();

67 68 69
        $qb->select('u.*', 'p.*')
           ->from('users', 'u')
           ->Join('u', 'phones', 'p', $expr->eq('p.user_id', 'u.id'));
70

71
        $this->assertEquals('SELECT u.*, p.* FROM users u INNER JOIN phones p ON p.user_id = u.id', (string) $qb);
72
    }
73

74 75 76
    public function testSelectWithInnerJoin()
    {
        $qb   = new QueryBuilder($this->conn);
77 78
        $expr = $qb->expr();

79 80 81
        $qb->select('u.*', 'p.*')
           ->from('users', 'u')
           ->innerJoin('u', 'phones', 'p', $expr->eq('p.user_id', 'u.id'));
82

83
        $this->assertEquals('SELECT u.*, p.* FROM users u INNER JOIN phones p ON p.user_id = u.id', (string) $qb);
84
    }
85

86 87 88 89
    public function testSelectWithRightJoin()
    {
        $qb   = new QueryBuilder($this->conn);
        $expr = $qb->expr();
90

91 92 93
        $qb->select('u.*', 'p.*')
           ->from('users', 'u')
           ->rightJoin('u', 'phones', 'p', $expr->eq('p.user_id', 'u.id'));
94

95
        $this->assertEquals('SELECT u.*, p.* FROM users u RIGHT JOIN phones p ON p.user_id = u.id', (string) $qb);
96
    }
97

98 99 100 101
    public function testSelectWithAndWhereConditions()
    {
        $qb   = new QueryBuilder($this->conn);
        $expr = $qb->expr();
102

103 104 105 106
        $qb->select('u.*', 'p.*')
           ->from('users', 'u')
           ->where('u.username = ?')
           ->andWhere('u.name = ?');
107

108
        $this->assertEquals('SELECT u.*, p.* FROM users u WHERE (u.username = ?) AND (u.name = ?)', (string) $qb);
109
    }
110

111 112 113 114
    public function testSelectWithOrWhereConditions()
    {
        $qb   = new QueryBuilder($this->conn);
        $expr = $qb->expr();
115

116 117 118 119
        $qb->select('u.*', 'p.*')
           ->from('users', 'u')
           ->where('u.username = ?')
           ->orWhere('u.name = ?');
120

121
        $this->assertEquals('SELECT u.*, p.* FROM users u WHERE (u.username = ?) OR (u.name = ?)', (string) $qb);
122
    }
123

124 125 126 127
    public function testSelectWithOrOrWhereConditions()
    {
        $qb   = new QueryBuilder($this->conn);
        $expr = $qb->expr();
128

129 130 131 132
        $qb->select('u.*', 'p.*')
           ->from('users', 'u')
           ->orWhere('u.username = ?')
           ->orWhere('u.name = ?');
133

134
        $this->assertEquals('SELECT u.*, p.* FROM users u WHERE (u.username = ?) OR (u.name = ?)', (string) $qb);
135
    }
136

137 138 139 140
    public function testSelectWithAndOrWhereConditions()
    {
        $qb   = new QueryBuilder($this->conn);
        $expr = $qb->expr();
141

142 143 144 145 146 147
        $qb->select('u.*', 'p.*')
           ->from('users', 'u')
           ->where('u.username = ?')
           ->andWhere('u.username = ?')
           ->orWhere('u.name = ?')
           ->andWhere('u.name = ?');
148

149
        $this->assertEquals('SELECT u.*, p.* FROM users u WHERE (((u.username = ?) AND (u.username = ?)) OR (u.name = ?)) AND (u.name = ?)', (string) $qb);
150
    }
151

152 153 154 155
    public function testSelectGroupBy()
    {
        $qb   = new QueryBuilder($this->conn);
        $expr = $qb->expr();
156

157 158 159
        $qb->select('u.*', 'p.*')
           ->from('users', 'u')
           ->groupBy('u.id');
160

161
        $this->assertEquals('SELECT u.*, p.* FROM users u GROUP BY u.id', (string) $qb);
162
    }
163

164 165 166 167
    public function testSelectEmptyGroupBy()
    {
        $qb   = new QueryBuilder($this->conn);
        $expr = $qb->expr();
168

169 170 171
        $qb->select('u.*', 'p.*')
           ->groupBy(array())
           ->from('users', 'u');
172

173
        $this->assertEquals('SELECT u.*, p.* FROM users u', (string) $qb);
174
    }
175

176 177 178 179
    public function testSelectEmptyAddGroupBy()
    {
        $qb   = new QueryBuilder($this->conn);
        $expr = $qb->expr();
180

181 182 183
        $qb->select('u.*', 'p.*')
           ->addGroupBy(array())
           ->from('users', 'u');
184

185
        $this->assertEquals('SELECT u.*, p.* FROM users u', (string) $qb);
186
    }
187

188 189 190 191
    public function testSelectAddGroupBy()
    {
        $qb   = new QueryBuilder($this->conn);
        $expr = $qb->expr();
192

193 194 195 196
        $qb->select('u.*', 'p.*')
           ->from('users', 'u')
           ->groupBy('u.id')
           ->addGroupBy('u.foo');
197

198
        $this->assertEquals('SELECT u.*, p.* FROM users u GROUP BY u.id, u.foo', (string) $qb);
199
    }
200

201 202 203 204
    public function testSelectAddGroupBys()
    {
        $qb   = new QueryBuilder($this->conn);
        $expr = $qb->expr();
205

206 207 208 209
        $qb->select('u.*', 'p.*')
           ->from('users', 'u')
           ->groupBy('u.id')
           ->addGroupBy('u.foo', 'u.bar');
210

211
        $this->assertEquals('SELECT u.*, p.* FROM users u GROUP BY u.id, u.foo, u.bar', (string) $qb);
212
    }
213

214 215 216 217
    public function testSelectHaving()
    {
        $qb   = new QueryBuilder($this->conn);
        $expr = $qb->expr();
218

219 220 221 222
        $qb->select('u.*', 'p.*')
           ->from('users', 'u')
           ->groupBy('u.id')
           ->having('u.name = ?');
223

224
        $this->assertEquals('SELECT u.*, p.* FROM users u GROUP BY u.id HAVING u.name = ?', (string) $qb);
225
    }
226

227 228 229 230
    public function testSelectAndHaving()
    {
        $qb   = new QueryBuilder($this->conn);
        $expr = $qb->expr();
231

232 233 234 235
        $qb->select('u.*', 'p.*')
           ->from('users', 'u')
           ->groupBy('u.id')
           ->andHaving('u.name = ?');
236

237
        $this->assertEquals('SELECT u.*, p.* FROM users u GROUP BY u.id HAVING u.name = ?', (string) $qb);
238
    }
239

240 241 242 243
    public function testSelectHavingAndHaving()
    {
        $qb   = new QueryBuilder($this->conn);
        $expr = $qb->expr();
244

245 246 247 248 249
        $qb->select('u.*', 'p.*')
           ->from('users', 'u')
           ->groupBy('u.id')
           ->having('u.name = ?')
           ->andHaving('u.username = ?');
250

251
        $this->assertEquals('SELECT u.*, p.* FROM users u GROUP BY u.id HAVING (u.name = ?) AND (u.username = ?)', (string) $qb);
252
    }
253

254 255 256 257
    public function testSelectHavingOrHaving()
    {
        $qb   = new QueryBuilder($this->conn);
        $expr = $qb->expr();
258

259 260 261 262 263
        $qb->select('u.*', 'p.*')
           ->from('users', 'u')
           ->groupBy('u.id')
           ->having('u.name = ?')
           ->orHaving('u.username = ?');
264

265
        $this->assertEquals('SELECT u.*, p.* FROM users u GROUP BY u.id HAVING (u.name = ?) OR (u.username = ?)', (string) $qb);
266
    }
267

268 269 270 271
    public function testSelectOrHavingOrHaving()
    {
        $qb   = new QueryBuilder($this->conn);
        $expr = $qb->expr();
272

273 274 275 276 277
        $qb->select('u.*', 'p.*')
           ->from('users', 'u')
           ->groupBy('u.id')
           ->orHaving('u.name = ?')
           ->orHaving('u.username = ?');
278

279
        $this->assertEquals('SELECT u.*, p.* FROM users u GROUP BY u.id HAVING (u.name = ?) OR (u.username = ?)', (string) $qb);
280
    }
281

282 283 284 285
    public function testSelectHavingAndOrHaving()
    {
        $qb   = new QueryBuilder($this->conn);
        $expr = $qb->expr();
286

287 288 289 290 291 292
        $qb->select('u.*', 'p.*')
           ->from('users', 'u')
           ->groupBy('u.id')
           ->having('u.name = ?')
           ->orHaving('u.username = ?')
           ->andHaving('u.username = ?');
293

294
        $this->assertEquals('SELECT u.*, p.* FROM users u GROUP BY u.id HAVING ((u.name = ?) OR (u.username = ?)) AND (u.username = ?)', (string) $qb);
295
    }
296

297 298 299 300
    public function testSelectOrderBy()
    {
        $qb   = new QueryBuilder($this->conn);
        $expr = $qb->expr();
301

302 303 304
        $qb->select('u.*', 'p.*')
           ->from('users', 'u')
           ->orderBy('u.name');
305

306
        $this->assertEquals('SELECT u.*, p.* FROM users u ORDER BY u.name ASC', (string) $qb);
307
    }
308

309 310 311 312
    public function testSelectAddOrderBy()
    {
        $qb   = new QueryBuilder($this->conn);
        $expr = $qb->expr();
313

314 315 316 317
        $qb->select('u.*', 'p.*')
           ->from('users', 'u')
           ->orderBy('u.name')
           ->addOrderBy('u.username', 'DESC');
318

319
        $this->assertEquals('SELECT u.*, p.* FROM users u ORDER BY u.name ASC, u.username DESC', (string) $qb);
320
    }
321

322 323 324 325
    public function testSelectAddAddOrderBy()
    {
        $qb   = new QueryBuilder($this->conn);
        $expr = $qb->expr();
326

327 328 329 330
        $qb->select('u.*', 'p.*')
           ->from('users', 'u')
           ->addOrderBy('u.name')
           ->addOrderBy('u.username', 'DESC');
331

332
        $this->assertEquals('SELECT u.*, p.* FROM users u ORDER BY u.name ASC, u.username DESC', (string) $qb);
333
    }
334

335 336 337 338
    public function testEmptySelect()
    {
        $qb   = new QueryBuilder($this->conn);
        $qb2 = $qb->select();
339

340 341 342
        $this->assertSame($qb, $qb2);
        $this->assertEquals(QueryBuilder::SELECT, $qb->getType());
    }
343

344 345 346 347
    public function testSelectAddSelect()
    {
        $qb   = new QueryBuilder($this->conn);
        $expr = $qb->expr();
348

349 350 351
        $qb->select('u.*')
           ->addSelect('p.*')
           ->from('users', 'u');
352

353
        $this->assertEquals('SELECT u.*, p.* FROM users u', (string) $qb);
354
    }
355

356 357 358 359
    public function testEmptyAddSelect()
    {
        $qb   = new QueryBuilder($this->conn);
        $qb2 = $qb->addSelect();
360

361 362 363
        $this->assertSame($qb, $qb2);
        $this->assertEquals(QueryBuilder::SELECT, $qb->getType());
    }
364

365 366 367 368
    public function testSelectMultipleFrom()
    {
        $qb   = new QueryBuilder($this->conn);
        $expr = $qb->expr();
369

370 371 372 373
        $qb->select('u.*')
           ->addSelect('p.*')
           ->from('users', 'u')
           ->from('phonenumbers', 'p');
374

375
        $this->assertEquals('SELECT u.*, p.* FROM users u, phonenumbers p', (string) $qb);
376
    }
377

378 379 380 381 382 383
    public function testUpdate()
    {
        $qb   = new QueryBuilder($this->conn);
        $qb->update('users', 'u')
           ->set('u.foo', '?')
           ->set('u.bar', '?');
384

385
        $this->assertEquals(QueryBuilder::UPDATE, $qb->getType());
386
        $this->assertEquals('UPDATE users u SET u.foo = ?, u.bar = ?', (string) $qb);
387
    }
388

389 390 391 392 393 394
    public function testUpdateWithoutAlias()
    {
        $qb   = new QueryBuilder($this->conn);
        $qb->update('users')
           ->set('foo', '?')
           ->set('bar', '?');
395

396
        $this->assertEquals('UPDATE users SET foo = ?, bar = ?', (string) $qb);
397
    }
398

399 400 401 402 403 404
    public function testUpdateWhere()
    {
        $qb   = new QueryBuilder($this->conn);
        $qb->update('users', 'u')
           ->set('u.foo', '?')
           ->where('u.foo = ?');
405

406
        $this->assertEquals('UPDATE users u SET u.foo = ? WHERE u.foo = ?', (string) $qb);
407
    }
408

409 410 411 412
    public function testEmptyUpdate()
    {
        $qb   = new QueryBuilder($this->conn);
        $qb2 = $qb->update();
413

414 415 416
        $this->assertEquals(QueryBuilder::UPDATE, $qb->getType());
        $this->assertSame($qb2, $qb);
    }
417

418 419 420 421
    public function testDelete()
    {
        $qb   = new QueryBuilder($this->conn);
        $qb->delete('users', 'u');
422

423
        $this->assertEquals(QueryBuilder::DELETE, $qb->getType());
424
        $this->assertEquals('DELETE FROM users u', (string) $qb);
425
    }
426

427 428 429 430
    public function testDeleteWithoutAlias()
    {
        $qb   = new QueryBuilder($this->conn);
        $qb->delete('users');
431

432
        $this->assertEquals(QueryBuilder::DELETE, $qb->getType());
433
        $this->assertEquals('DELETE FROM users', (string) $qb);
434
    }
435

436 437 438 439 440
    public function testDeleteWhere()
    {
        $qb   = new QueryBuilder($this->conn);
        $qb->delete('users', 'u')
           ->where('u.foo = ?');
441

442
        $this->assertEquals('DELETE FROM users u WHERE u.foo = ?', (string) $qb);
443
    }
444

445 446 447 448
    public function testEmptyDelete()
    {
        $qb   = new QueryBuilder($this->conn);
        $qb2 = $qb->delete();
449

450 451 452
        $this->assertEquals(QueryBuilder::DELETE, $qb->getType());
        $this->assertSame($qb2, $qb);
    }
453

454 455 456 457 458
    public function testGetConnection()
    {
        $qb   = new QueryBuilder($this->conn);
        $this->assertSame($this->conn, $qb->getConnection());
    }
459

460 461 462
    public function testGetState()
    {
        $qb   = new QueryBuilder($this->conn);
463

464
        $this->assertEquals(QueryBuilder::STATE_CLEAN, $qb->getState());
465

466
        $qb->select('u.*')->from('users', 'u');
467

468
        $this->assertEquals(QueryBuilder::STATE_DIRTY, $qb->getState());
469

470
        $sql1 = $qb->getSQL();
471

472 473 474
        $this->assertEquals(QueryBuilder::STATE_CLEAN, $qb->getState());
        $this->assertEquals($sql1, $qb->getSQL());
    }
475

476 477 478 479
    public function testSetMaxResults()
    {
        $qb   = new QueryBuilder($this->conn);
        $qb->setMaxResults(10);
480

481 482 483
        $this->assertEquals(QueryBuilder::STATE_DIRTY, $qb->getState());
        $this->assertEQuals(10, $qb->getMaxResults());
    }
484

485 486 487 488
    public function testSetFirstResult()
    {
        $qb   = new QueryBuilder($this->conn);
        $qb->setFirstResult(10);
489

490 491 492
        $this->assertEquals(QueryBuilder::STATE_DIRTY, $qb->getState());
        $this->assertEQuals(10, $qb->getFirstResult());
    }
493

494 495 496
    public function testResetQueryPart()
    {
        $qb   = new QueryBuilder($this->conn);
497

498
        $qb->select('u.*')->from('users', 'u')->where('u.name = ?');
499

500
        $this->assertEquals('SELECT u.* FROM users u WHERE u.name = ?', (string)$qb);
501
        $qb->resetQueryPart('where');
502
        $this->assertEquals('SELECT u.* FROM users u', (string)$qb);
503
    }
504

505 506 507
    public function testResetQueryParts()
    {
        $qb   = new QueryBuilder($this->conn);
508

509
        $qb->select('u.*')->from('users', 'u')->where('u.name = ?')->orderBy('u.name');
510

511
        $this->assertEquals('SELECT u.* FROM users u WHERE u.name = ? ORDER BY u.name ASC', (string)$qb);
512
        $qb->resetQueryParts(array('where', 'orderBy'));
513
        $this->assertEquals('SELECT u.* FROM users u', (string)$qb);
514
    }
515

516 517 518
    public function testCreateNamedParameter()
    {
        $qb   = new QueryBuilder($this->conn);
519

520 521 522
        $qb->select('u.*')->from('users', 'u')->where(
            $qb->expr()->eq('u.name', $qb->createNamedParameter(10, \PDO::PARAM_INT))
        );
523

524
        $this->assertEquals('SELECT u.* FROM users u WHERE u.name = :dcValue1', (string)$qb);
525 526
        $this->assertEquals(10, $qb->getParameter('dcValue1'));
    }
527

528 529 530
    public function testCreateNamedParameterCustomPlaceholder()
    {
        $qb   = new QueryBuilder($this->conn);
531

532 533 534
        $qb->select('u.*')->from('users', 'u')->where(
            $qb->expr()->eq('u.name', $qb->createNamedParameter(10, \PDO::PARAM_INT, ':test'))
        );
535

536
        $this->assertEquals('SELECT u.* FROM users u WHERE u.name = :test', (string)$qb);
537 538
        $this->assertEquals(10, $qb->getParameter('test'));
    }
539

540 541 542
    public function testCreatePositionalParameter()
    {
        $qb   = new QueryBuilder($this->conn);
543

544 545 546
        $qb->select('u.*')->from('users', 'u')->where(
            $qb->expr()->eq('u.name', $qb->createPositionalParameter(10, \PDO::PARAM_INT))
        );
547

548
        $this->assertEquals('SELECT u.* FROM users u WHERE u.name = ?', (string)$qb);
549 550
        $this->assertEquals(10, $qb->getParameter(1));
    }
551 552 553 554 555 556 557 558

    /**
     * @group DBAL-172
     */
    public function testReferenceJoinFromJoin()
    {
        $qb = new QueryBuilder($this->conn);

Wim Vandersmissen's avatar
Wim Vandersmissen committed
559 560 561 562 563 564 565
        $qb->select('COUNT(DISTINCT news.id)')
            ->from('cb_newspages', 'news')
            ->innerJoin('news', 'nodeversion', 'nv', 'nv.refId = news.id AND nv.refEntityname=\'News\'')
            ->innerJoin('invalid', 'nodetranslation', 'nt', 'nv.nodetranslation = nt.id')
            ->innerJoin('nt', 'node', 'n', 'nt.node = n.id')
            ->where('nt.lang = :lang AND n.deleted != 1');

Deni's avatar
Deni committed
566
        $this->setExpectedException('Doctrine\DBAL\Query\QueryException', "The given alias 'invalid' is not part of any FROM or JOIN clause table. The currently registered aliases are: news, nv.");
567 568
        $this->assertEquals('', $qb->getSQL());
    }
569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584

    /**
     * @group DBAL-172
     */
    public function testSelectFromMasterWithWhereOnJoinedTables()
    {
        $qb = new QueryBuilder($this->conn);

        $qb->select('COUNT(DISTINCT news.id)')
            ->from('newspages', 'news')
            ->innerJoin('news', 'nodeversion', 'nv', "nv.refId = news.id AND nv.refEntityname='Entity\\News'")
            ->innerJoin('nv', 'nodetranslation', 'nt', 'nv.nodetranslation = nt.id')
            ->innerJoin('nt', 'node', 'n', 'nt.node = n.id')
            ->where('nt.lang = ?')
            ->andWhere('n.deleted = 0');

585
        $this->assertEquals("SELECT COUNT(DISTINCT news.id) FROM newspages news INNER JOIN nodeversion nv ON nv.refId = news.id AND nv.refEntityname='Entity\\News' INNER JOIN nodetranslation nt ON nv.nodetranslation = nt.id INNER JOIN node n ON nt.node = n.id WHERE (nt.lang = ?) AND (n.deleted = 0)", $qb->getSQL());
586
    }
Deni's avatar
Deni committed
587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602

    /**
     * @group DBAL-442
     */
    public function testSelectWithMultipleFromAndJoins()
    {
        $qb = new QueryBuilder($this->conn);

        $qb->select('DISTINCT u.id')
            ->from('users', 'u')
            ->from('articles', 'a')
            ->innerJoin('u', 'permissions', 'p', 'p.user_id = u.id')
            ->innerJoin('a', 'comments', 'c', 'c.article_id = a.id')
            ->where('u.id = a.user_id')
            ->andWhere('p.read = 1');

603
        $this->assertEquals('SELECT DISTINCT u.id FROM users u INNER JOIN permissions p ON p.user_id = u.id, articles a INNER JOIN comments c ON c.article_id = a.id WHERE (u.id = a.user_id) AND (p.read = 1)', $qb->getSQL());
Deni's avatar
Deni committed
604
    }
Paul's avatar
Paul committed
605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625

    public function testClone()
    {
        $qb = new QueryBuilder($this->conn);

        $qb->select('u.id')
            ->from('users', 'u')
            ->where('u.id = :test');

        $qb->setParameter(':test', (object) 1);

        $qb_clone = clone $qb;

        $this->assertEquals((string) $qb, (string) $qb_clone);

        $qb->andWhere('u.id = 1');

        $this->assertFalse($qb->getQueryParts() === $qb_clone->getQueryParts());
        $this->assertFalse($qb->getParameters() === $qb_clone->getParameters());
    }
}