QueryBuilderTest.php 26.9 KB
Newer Older
1 2 3 4
<?php

namespace Doctrine\Tests\DBAL\Query;

jeroendedauw's avatar
jeroendedauw committed
5 6
use Doctrine\DBAL\Query\Expression\ExpressionBuilder;
use Doctrine\DBAL\Query\QueryBuilder;
7

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

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

19
        $expressionBuilder = new ExpressionBuilder($this->conn);
20

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

448 449
        $this->assertEquals(QueryBuilder::DELETE, $qb->getType());
        $this->assertSame($qb2, $qb);
Steve Müller's avatar
Steve Müller committed
450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521
    }

    public function testInsertValues()
    {
        $qb = new QueryBuilder($this->conn);
        $qb->insert('users')
            ->values(
                array(
                    'foo' => '?',
                    'bar' => '?'
                )
            );

        $this->assertEquals(QueryBuilder::INSERT, $qb->getType());
        $this->assertEquals('INSERT INTO users (foo, bar) VALUES(?, ?)', (string) $qb);
    }

    public function testInsertReplaceValues()
    {
        $qb = new QueryBuilder($this->conn);
        $qb->insert('users')
            ->values(
                array(
                    'foo' => '?',
                    'bar' => '?'
                )
            )
            ->values(
                array(
                    'bar' => '?',
                    'foo' => '?'
                )
            );

        $this->assertEquals(QueryBuilder::INSERT, $qb->getType());
        $this->assertEquals('INSERT INTO users (bar, foo) VALUES(?, ?)', (string) $qb);
    }

    public function testInsertSetValue()
    {
        $qb = new QueryBuilder($this->conn);
        $qb->insert('users')
            ->setValue('foo', 'bar')
            ->setValue('bar', '?')
            ->setValue('foo', '?');

        $this->assertEquals(QueryBuilder::INSERT, $qb->getType());
        $this->assertEquals('INSERT INTO users (foo, bar) VALUES(?, ?)', (string) $qb);
    }

    public function testInsertValuesSetValue()
    {
        $qb = new QueryBuilder($this->conn);
        $qb->insert('users')
            ->values(
                array(
                    'foo' => '?'
                )
            )
            ->setValue('bar', '?');

        $this->assertEquals(QueryBuilder::INSERT, $qb->getType());
        $this->assertEquals('INSERT INTO users (foo, bar) VALUES(?, ?)', (string) $qb);
    }

    public function testEmptyInsert()
    {
        $qb = new QueryBuilder($this->conn);
        $qb2 = $qb->insert();

        $this->assertEquals(QueryBuilder::INSERT, $qb->getType());
        $this->assertSame($qb2, $qb);
522
    }
523

524 525 526 527 528
    public function testGetConnection()
    {
        $qb   = new QueryBuilder($this->conn);
        $this->assertSame($this->conn, $qb->getConnection());
    }
529

530 531 532
    public function testGetState()
    {
        $qb   = new QueryBuilder($this->conn);
533

534
        $this->assertEquals(QueryBuilder::STATE_CLEAN, $qb->getState());
535

536
        $qb->select('u.*')->from('users', 'u');
537

538
        $this->assertEquals(QueryBuilder::STATE_DIRTY, $qb->getState());
539

540
        $sql1 = $qb->getSQL();
541

542 543 544
        $this->assertEquals(QueryBuilder::STATE_CLEAN, $qb->getState());
        $this->assertEquals($sql1, $qb->getSQL());
    }
545

546 547 548 549
    public function testSetMaxResults()
    {
        $qb   = new QueryBuilder($this->conn);
        $qb->setMaxResults(10);
550

551 552 553
        $this->assertEquals(QueryBuilder::STATE_DIRTY, $qb->getState());
        $this->assertEQuals(10, $qb->getMaxResults());
    }
554

555 556 557 558
    public function testSetFirstResult()
    {
        $qb   = new QueryBuilder($this->conn);
        $qb->setFirstResult(10);
559

560 561 562
        $this->assertEquals(QueryBuilder::STATE_DIRTY, $qb->getState());
        $this->assertEQuals(10, $qb->getFirstResult());
    }
563

564 565 566
    public function testResetQueryPart()
    {
        $qb   = new QueryBuilder($this->conn);
567

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

570
        $this->assertEquals('SELECT u.* FROM users u WHERE u.name = ?', (string)$qb);
571
        $qb->resetQueryPart('where');
572
        $this->assertEquals('SELECT u.* FROM users u', (string)$qb);
573
    }
574

575 576 577
    public function testResetQueryParts()
    {
        $qb   = new QueryBuilder($this->conn);
578

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

581
        $this->assertEquals('SELECT u.* FROM users u WHERE u.name = ? ORDER BY u.name ASC', (string)$qb);
582
        $qb->resetQueryParts(array('where', 'orderBy'));
583
        $this->assertEquals('SELECT u.* FROM users u', (string)$qb);
584
    }
585

586 587 588
    public function testCreateNamedParameter()
    {
        $qb   = new QueryBuilder($this->conn);
589

590 591 592
        $qb->select('u.*')->from('users', 'u')->where(
            $qb->expr()->eq('u.name', $qb->createNamedParameter(10, \PDO::PARAM_INT))
        );
593

594
        $this->assertEquals('SELECT u.* FROM users u WHERE u.name = :dcValue1', (string)$qb);
595
        $this->assertEquals(10, $qb->getParameter('dcValue1'));
596
        $this->assertEquals(\PDO::PARAM_INT, $qb->getParameterType('dcValue1'));
597
    }
598

599 600 601
    public function testCreateNamedParameterCustomPlaceholder()
    {
        $qb   = new QueryBuilder($this->conn);
602

603 604 605
        $qb->select('u.*')->from('users', 'u')->where(
            $qb->expr()->eq('u.name', $qb->createNamedParameter(10, \PDO::PARAM_INT, ':test'))
        );
606

607
        $this->assertEquals('SELECT u.* FROM users u WHERE u.name = :test', (string)$qb);
608
        $this->assertEquals(10, $qb->getParameter('test'));
609
        $this->assertEquals(\PDO::PARAM_INT, $qb->getParameterType('test'));
610
    }
611

612 613 614
    public function testCreatePositionalParameter()
    {
        $qb   = new QueryBuilder($this->conn);
615

616 617 618
        $qb->select('u.*')->from('users', 'u')->where(
            $qb->expr()->eq('u.name', $qb->createPositionalParameter(10, \PDO::PARAM_INT))
        );
619

620
        $this->assertEquals('SELECT u.* FROM users u WHERE u.name = ?', (string)$qb);
621
        $this->assertEquals(10, $qb->getParameter(1));
622
        $this->assertEquals(\PDO::PARAM_INT, $qb->getParameterType(1));
623
    }
624 625 626 627 628 629 630 631

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

Wim Vandersmissen's avatar
Wim Vandersmissen committed
632 633 634 635 636 637 638
        $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
639
        $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.");
640 641
        $this->assertEquals('', $qb->getSQL());
    }
642 643 644 645 646 647 648 649 650 651 652 653 654 655 656 657

    /**
     * @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');

658
        $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());
659
    }
Deni's avatar
Deni committed
660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675

    /**
     * @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');

676
        $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
677
    }
Paul's avatar
Paul committed
678

Jeroen Thora's avatar
Jeroen Thora committed
679 680 681
    /**
     * @group DBAL-774
     */
682 683 684 685 686 687 688 689 690 691 692
    public function testSelectWithJoinsWithMultipleOnConditionsParseOrder()
    {
        $qb = new QueryBuilder($this->conn);

        $qb->select('a.id')
            ->from('table_a', 'a')
            ->join('a', 'table_b', 'b', 'a.fk_b = b.id')
            ->join('b', 'table_c', 'c', 'c.fk_b = b.id AND b.language = ?')
            ->join('a', 'table_d', 'd', 'a.fk_d = d.id')
            ->join('c', 'table_e', 'e', 'e.fk_c = c.id AND e.fk_d = d.id');

693 694 695 696 697 698 699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 714 715 716 717 718 719 720 721 722 723 724 725 726 727 728 729 730
        $this->assertEquals(
            'SELECT a.id ' .
            'FROM table_a a ' .
            'INNER JOIN table_b b ON a.fk_b = b.id ' .
            'INNER JOIN table_d d ON a.fk_d = d.id ' .
            'INNER JOIN table_c c ON c.fk_b = b.id AND b.language = ? ' .
            'INNER JOIN table_e e ON e.fk_c = c.id AND e.fk_d = d.id',
            (string) $qb
        );
    }

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

        $qb->select('a.id')
            ->from('table_a', 'a')
            ->from('table_f', 'f')
            ->join('a', 'table_b', 'b', 'a.fk_b = b.id')
            ->join('b', 'table_c', 'c', 'c.fk_b = b.id AND b.language = ?')
            ->join('a', 'table_d', 'd', 'a.fk_d = d.id')
            ->join('c', 'table_e', 'e', 'e.fk_c = c.id AND e.fk_d = d.id')
            ->join('f', 'table_g', 'g', 'f.fk_g = g.id');

        $this->assertEquals(
            'SELECT a.id ' .
            'FROM table_a a ' .
            'INNER JOIN table_b b ON a.fk_b = b.id ' .
            'INNER JOIN table_d d ON a.fk_d = d.id ' .
            'INNER JOIN table_c c ON c.fk_b = b.id AND b.language = ? ' .
            'INNER JOIN table_e e ON e.fk_c = c.id AND e.fk_d = d.id, ' .
            'table_f f ' .
            'INNER JOIN table_g g ON f.fk_g = g.id',
            (string) $qb
        );
731 732
    }

Paul's avatar
Paul committed
733 734 735 736 737 738 739 740 741 742 743 744 745 746 747 748 749 750 751
    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());
    }
752 753 754 755 756 757 758 759 760 761 762 763 764 765 766 767 768 769 770 771 772 773 774 775 776 777 778 779 780 781 782 783 784 785 786 787 788 789 790 791 792 793 794 795 796 797 798 799 800

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

        $qb->select('id')
            ->from('users');

        $this->assertEquals('SELECT id FROM users', (string) $qb);
    }

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

        $qb->select('id', 'name')
            ->from('users')
            ->where('awesome=9001');

        $this->assertEquals("SELECT id, name FROM users WHERE awesome=9001", (string) $qb);
    }

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

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

        $this->assertEquals('SELECT DISTINCT users.id FROM users INNER JOIN permissions p ON p.user_id = users.id, articles INNER JOIN comments c ON c.article_id = articles.id WHERE (users.id = articles.user_id) AND (p.read = 1)', $qb->getSQL());
    }

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

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

        $this->assertEquals('SELECT u.id FROM users u INNER JOIN permissions p ON p.user_id = u.id, articles INNER JOIN comments c ON c.article_id = articles.id', $qb->getSQL());
    }
801 802 803 804 805 806 807 808 809 810 811 812 813 814 815 816 817 818 819 820

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

        $qb->select('users.*')
            ->from('users');

        $this->assertEquals("SELECT users.* FROM users", (string) $qb);
    }

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

        $qb->select('*')
            ->from('users');

        $this->assertEquals("SELECT * FROM users", (string) $qb);
    }
821 822 823 824 825 826 827 828 829 830 831 832 833 834 835 836 837 838 839 840 841 842 843 844 845 846 847 848 849 850 851 852 853 854 855 856 857 858 859 860 861 862 863 864 865

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

        $qb->select('*')->from('users');

        $this->assertNull($qb->getParameterType('name'));

        $qb->where('name = :name');
        $qb->setParameter('name', 'foo');

        $this->assertNull($qb->getParameterType('name'));

        $qb->setParameter('name', 'foo', \PDO::PARAM_STR);

        $this->assertSame(\PDO::PARAM_STR, $qb->getParameterType('name'));
    }

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

        $qb->select('*')->from('users');

        $this->assertSame(array(), $qb->getParameterTypes());

        $qb->where('name = :name');
        $qb->setParameter('name', 'foo');

        $this->assertSame(array(), $qb->getParameterTypes());

        $qb->setParameter('name', 'foo', \PDO::PARAM_STR);

        $qb->where('is_active = :isActive');
        $qb->setParameter('isActive', true, \PDO::PARAM_BOOL);

        $this->assertSame(array('name' => \PDO::PARAM_STR, 'isActive' => \PDO::PARAM_BOOL), $qb->getParameterTypes());
    }
jarekj's avatar
jarekj committed
866 867

    /**
Steve Müller's avatar
Steve Müller committed
868
     * @group DBAL-1137
jarekj's avatar
jarekj committed
869
     */
870
    public function testJoinWithNonUniqueAliasThrowsException()
jarekj's avatar
jarekj committed
871 872 873
    {
        $qb = new QueryBuilder($this->conn);

Steve Müller's avatar
Steve Müller committed
874
        $qb->select('a.id')
jarekj's avatar
jarekj committed
875
            ->from('table_a', 'a')
Steve Müller's avatar
Steve Müller committed
876
            ->join('a', 'table_b', 'a', 'a.fk_b = a.id');
jarekj's avatar
jarekj committed
877

Steve Müller's avatar
Steve Müller committed
878 879
        $this->setExpectedException(
            'Doctrine\DBAL\Query\QueryException',
Steve Müller's avatar
Steve Müller committed
880
            "The given alias 'a' is not unique in FROM and JOIN clause table. The currently registered aliases are: a."
jarekj's avatar
jarekj committed
881
        );
Steve Müller's avatar
Steve Müller committed
882 883

        $qb->getSQL();
jarekj's avatar
jarekj committed
884
    }
Paul's avatar
Paul committed
885
}