QueryBuilderTest.php 27.1 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
    protected function setUp()
16
    {
17
        $this->conn = $this->createMock('Doctrine\DBAL\Connection');
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 29 30 31 32 33 34 35 36 37
    /**
     * @group DBAL-2291
     */
    public function testSimpleSelectWithoutFrom()
    {
        $qb = new QueryBuilder($this->conn);

        $qb->select('some_function()');

        $this->assertEquals('SELECT some_function()', (string) $qb);
    }

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

42 43
        $qb->select('u.id')
           ->from('users', 'u');
44

45
        $this->assertEquals('SELECT u.id FROM users u', (string) $qb);
46
    }
47

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

53 54 55
        $qb->select('u.id')
           ->from('users', 'u')
           ->where($expr->andX($expr->eq('u.nickname', '?')));
56

57
        $this->assertEquals("SELECT u.id FROM users u WHERE u.nickname = ?", (string) $qb);
58
    }
59

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

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

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

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

77 78 79
        $qb->select('u.*', 'p.*')
           ->from('users', 'u')
           ->Join('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
    public function testSelectWithInnerJoin()
    {
        $qb   = new QueryBuilder($this->conn);
87 88
        $expr = $qb->expr();

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

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

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

101 102 103
        $qb->select('u.*', 'p.*')
           ->from('users', 'u')
           ->rightJoin('u', 'phones', 'p', $expr->eq('p.user_id', 'u.id'));
104

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

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

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

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

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

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

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

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

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

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

147 148 149 150
    public function testSelectWithAndOrWhereConditions()
    {
        $qb   = new QueryBuilder($this->conn);
        $expr = $qb->expr();
151

152 153 154 155 156 157
        $qb->select('u.*', 'p.*')
           ->from('users', 'u')
           ->where('u.username = ?')
           ->andWhere('u.username = ?')
           ->orWhere('u.name = ?')
           ->andWhere('u.name = ?');
158

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

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

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

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

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

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

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

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

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

195
        $this->assertEquals('SELECT u.*, p.* FROM users u', (string) $qb);
196
    }
197

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

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

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

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

216 217 218 219
        $qb->select('u.*', 'p.*')
           ->from('users', 'u')
           ->groupBy('u.id')
           ->addGroupBy('u.foo', 'u.bar');
220

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

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

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

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

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

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

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

250 251 252 253
    public function testSelectHavingAndHaving()
    {
        $qb   = new QueryBuilder($this->conn);
        $expr = $qb->expr();
254

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

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

264 265 266 267
    public function testSelectHavingOrHaving()
    {
        $qb   = new QueryBuilder($this->conn);
        $expr = $qb->expr();
268

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

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

278 279 280 281
    public function testSelectOrHavingOrHaving()
    {
        $qb   = new QueryBuilder($this->conn);
        $expr = $qb->expr();
282

283 284 285 286 287
        $qb->select('u.*', 'p.*')
           ->from('users', 'u')
           ->groupBy('u.id')
           ->orHaving('u.name = ?')
           ->orHaving('u.username = ?');
288

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

292 293 294 295
    public function testSelectHavingAndOrHaving()
    {
        $qb   = new QueryBuilder($this->conn);
        $expr = $qb->expr();
296

297 298 299 300 301 302
        $qb->select('u.*', 'p.*')
           ->from('users', 'u')
           ->groupBy('u.id')
           ->having('u.name = ?')
           ->orHaving('u.username = ?')
           ->andHaving('u.username = ?');
303

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

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

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

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

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

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

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

332 333 334 335
    public function testSelectAddAddOrderBy()
    {
        $qb   = new QueryBuilder($this->conn);
        $expr = $qb->expr();
336

337 338 339 340
        $qb->select('u.*', 'p.*')
           ->from('users', 'u')
           ->addOrderBy('u.name')
           ->addOrderBy('u.username', 'DESC');
341

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

345 346 347 348
    public function testEmptySelect()
    {
        $qb   = new QueryBuilder($this->conn);
        $qb2 = $qb->select();
349

350 351 352
        $this->assertSame($qb, $qb2);
        $this->assertEquals(QueryBuilder::SELECT, $qb->getType());
    }
353

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

359 360 361
        $qb->select('u.*')
           ->addSelect('p.*')
           ->from('users', 'u');
362

363
        $this->assertEquals('SELECT u.*, p.* FROM users u', (string) $qb);
364
    }
365

366 367 368 369
    public function testEmptyAddSelect()
    {
        $qb   = new QueryBuilder($this->conn);
        $qb2 = $qb->addSelect();
370

371 372 373
        $this->assertSame($qb, $qb2);
        $this->assertEquals(QueryBuilder::SELECT, $qb->getType());
    }
374

375 376 377 378
    public function testSelectMultipleFrom()
    {
        $qb   = new QueryBuilder($this->conn);
        $expr = $qb->expr();
379

380 381 382 383
        $qb->select('u.*')
           ->addSelect('p.*')
           ->from('users', 'u')
           ->from('phonenumbers', 'p');
384

385
        $this->assertEquals('SELECT u.*, p.* FROM users u, phonenumbers p', (string) $qb);
386
    }
387

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

395
        $this->assertEquals(QueryBuilder::UPDATE, $qb->getType());
396
        $this->assertEquals('UPDATE users u SET u.foo = ?, u.bar = ?', (string) $qb);
397
    }
398

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

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

409 410 411 412 413 414
    public function testUpdateWhere()
    {
        $qb   = new QueryBuilder($this->conn);
        $qb->update('users', 'u')
           ->set('u.foo', '?')
           ->where('u.foo = ?');
415

416
        $this->assertEquals('UPDATE users u SET u.foo = ? WHERE u.foo = ?', (string) $qb);
417
    }
418

419 420 421 422
    public function testEmptyUpdate()
    {
        $qb   = new QueryBuilder($this->conn);
        $qb2 = $qb->update();
423

424 425 426
        $this->assertEquals(QueryBuilder::UPDATE, $qb->getType());
        $this->assertSame($qb2, $qb);
    }
427

428 429 430 431
    public function testDelete()
    {
        $qb   = new QueryBuilder($this->conn);
        $qb->delete('users', 'u');
432

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

437 438 439 440
    public function testDeleteWithoutAlias()
    {
        $qb   = new QueryBuilder($this->conn);
        $qb->delete('users');
441

442
        $this->assertEquals(QueryBuilder::DELETE, $qb->getType());
443
        $this->assertEquals('DELETE FROM users', (string) $qb);
444
    }
445

446 447 448 449 450
    public function testDeleteWhere()
    {
        $qb   = new QueryBuilder($this->conn);
        $qb->delete('users', 'u')
           ->where('u.foo = ?');
451

452
        $this->assertEquals('DELETE FROM users u WHERE u.foo = ?', (string) $qb);
453
    }
454

455 456 457 458
    public function testEmptyDelete()
    {
        $qb   = new QueryBuilder($this->conn);
        $qb2 = $qb->delete();
459

460 461
        $this->assertEquals(QueryBuilder::DELETE, $qb->getType());
        $this->assertSame($qb2, $qb);
Steve Müller's avatar
Steve Müller committed
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 522 523 524 525 526 527 528 529 530 531 532 533
    }

    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);
534
    }
535

536 537 538 539 540
    public function testGetConnection()
    {
        $qb   = new QueryBuilder($this->conn);
        $this->assertSame($this->conn, $qb->getConnection());
    }
541

542 543 544
    public function testGetState()
    {
        $qb   = new QueryBuilder($this->conn);
545

546
        $this->assertEquals(QueryBuilder::STATE_CLEAN, $qb->getState());
547

548
        $qb->select('u.*')->from('users', 'u');
549

550
        $this->assertEquals(QueryBuilder::STATE_DIRTY, $qb->getState());
551

552
        $sql1 = $qb->getSQL();
553

554 555 556
        $this->assertEquals(QueryBuilder::STATE_CLEAN, $qb->getState());
        $this->assertEquals($sql1, $qb->getSQL());
    }
557

558 559 560 561
    public function testSetMaxResults()
    {
        $qb   = new QueryBuilder($this->conn);
        $qb->setMaxResults(10);
562

563 564 565
        $this->assertEquals(QueryBuilder::STATE_DIRTY, $qb->getState());
        $this->assertEQuals(10, $qb->getMaxResults());
    }
566

567 568 569 570
    public function testSetFirstResult()
    {
        $qb   = new QueryBuilder($this->conn);
        $qb->setFirstResult(10);
571

572 573 574
        $this->assertEquals(QueryBuilder::STATE_DIRTY, $qb->getState());
        $this->assertEQuals(10, $qb->getFirstResult());
    }
575

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

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

582
        $this->assertEquals('SELECT u.* FROM users u WHERE u.name = ?', (string)$qb);
583
        $qb->resetQueryPart('where');
584
        $this->assertEquals('SELECT u.* FROM users u', (string)$qb);
585
    }
586

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

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

593
        $this->assertEquals('SELECT u.* FROM users u WHERE u.name = ? ORDER BY u.name ASC', (string)$qb);
594
        $qb->resetQueryParts(array('where', 'orderBy'));
595
        $this->assertEquals('SELECT u.* FROM users u', (string)$qb);
596
    }
597

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

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

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

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

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

619
        $this->assertEquals('SELECT u.* FROM users u WHERE u.name = :test', (string)$qb);
620
        $this->assertEquals(10, $qb->getParameter('test'));
621
        $this->assertEquals(\PDO::PARAM_INT, $qb->getParameterType('test'));
622
    }
623

624 625 626
    public function testCreatePositionalParameter()
    {
        $qb   = new QueryBuilder($this->conn);
627

628 629 630
        $qb->select('u.*')->from('users', 'u')->where(
            $qb->expr()->eq('u.name', $qb->createPositionalParameter(10, \PDO::PARAM_INT))
        );
631

632
        $this->assertEquals('SELECT u.* FROM users u WHERE u.name = ?', (string)$qb);
633
        $this->assertEquals(10, $qb->getParameter(1));
634
        $this->assertEquals(\PDO::PARAM_INT, $qb->getParameterType(1));
635
    }
636 637 638 639 640 641 642 643

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

Wim Vandersmissen's avatar
Wim Vandersmissen committed
644 645 646 647 648 649 650
        $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
651
        $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.");
652 653
        $this->assertEquals('', $qb->getSQL());
    }
654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669

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

670
        $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());
671
    }
Deni's avatar
Deni committed
672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687

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

688
        $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
689
    }
Paul's avatar
Paul committed
690

Jeroen Thora's avatar
Jeroen Thora committed
691 692 693
    /**
     * @group DBAL-774
     */
694 695 696 697 698 699 700 701 702 703 704
    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');

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 731 732 733 734 735 736 737 738 739 740 741 742
        $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
        );
743 744
    }

Paul's avatar
Paul committed
745 746 747 748 749 750 751 752 753 754 755 756 757 758 759 760 761 762 763
    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());
    }
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 801 802 803 804 805 806 807 808 809 810 811 812

    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());
    }
813 814 815 816 817 818 819 820 821 822 823 824 825 826 827 828 829 830 831 832

    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);
    }
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 866 867 868 869 870 871 872 873 874 875 876 877

    /**
     * @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
878 879

    /**
Steve Müller's avatar
Steve Müller committed
880
     * @group DBAL-1137
jarekj's avatar
jarekj committed
881
     */
882
    public function testJoinWithNonUniqueAliasThrowsException()
jarekj's avatar
jarekj committed
883 884 885
    {
        $qb = new QueryBuilder($this->conn);

Steve Müller's avatar
Steve Müller committed
886
        $qb->select('a.id')
jarekj's avatar
jarekj committed
887
            ->from('table_a', 'a')
Steve Müller's avatar
Steve Müller committed
888
            ->join('a', 'table_b', 'a', 'a.fk_b = a.id');
jarekj's avatar
jarekj committed
889

Steve Müller's avatar
Steve Müller committed
890 891
        $this->setExpectedException(
            'Doctrine\DBAL\Query\QueryException',
Steve Müller's avatar
Steve Müller committed
892
            "The given alias 'a' is not unique in FROM and JOIN clause table. The currently registered aliases are: a."
jarekj's avatar
jarekj committed
893
        );
Steve Müller's avatar
Steve Müller committed
894 895

        $qb->getSQL();
jarekj's avatar
jarekj committed
896
    }
Paul's avatar
Paul committed
897
}