Yurii
699 lines
18 KiB
PHP
699 lines
18 KiB
PHP
<?php
|
||
/************************************************************************
|
||
* This file is part of EspoCRM.
|
||
*
|
||
* EspoCRM – Open Source CRM application.
|
||
* Copyright (C) 2014-2026 EspoCRM, Inc.
|
||
* Website: https://www.espocrm.com
|
||
*
|
||
* This program is free software: you can redistribute it and/or modify
|
||
* it under the terms of the GNU Affero General Public License as published by
|
||
* the Free Software Foundation, either version 3 of the License, or
|
||
* (at your option) any later version.
|
||
*
|
||
* This program is distributed in the hope that it will be useful,
|
||
* but WITHOUT ANY WARRANTY; without even the implied warranty of
|
||
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
|
||
* GNU Affero General Public License for more details.
|
||
*
|
||
* You should have received a copy of the GNU Affero General Public License
|
||
* along with this program. If not, see <https://www.gnu.org/licenses/>.
|
||
*
|
||
* The interactive user interfaces in modified source and object code versions
|
||
* of this program must display Appropriate Legal Notices, as required under
|
||
* Section 5 of the GNU Affero General Public License version 3.
|
||
*
|
||
* In accordance with Section 7(b) of the GNU Affero General Public License version 3,
|
||
* these Appropriate Legal Notices must retain the display of the "EspoCRM" word.
|
||
************************************************************************/
|
||
|
||
namespace tests\unit\Espo\ORM\Query;
|
||
|
||
use PHPUnit\Framework\TestCase;
|
||
use Espo\ORM\Query\Part\Condition as Cond;
|
||
use Espo\ORM\Query\Part\Expression as Expr;
|
||
use Espo\ORM\Query\Part\Join;
|
||
use Espo\ORM\Query\Part\Order;
|
||
use Espo\ORM\Query\Part\Selection;
|
||
use Espo\ORM\Query\Part\WhereClause;
|
||
use Espo\ORM\Query\SelectBuilder;
|
||
use RuntimeException;
|
||
|
||
class SelectBuilderTest extends TestCase
|
||
{
|
||
/**
|
||
* @var SelectBuilder
|
||
*/
|
||
private $builder;
|
||
|
||
protected function setUp(): void
|
||
{
|
||
$this->builder = new SelectBuilder();
|
||
}
|
||
|
||
public function testFrom()
|
||
{
|
||
$params = $this->builder
|
||
->from('Test')
|
||
->build()
|
||
->getRaw();
|
||
|
||
$this->assertEquals('Test', $params['from']);
|
||
}
|
||
|
||
public function testSelect1()
|
||
{
|
||
$select = $this->builder
|
||
->from('Test')
|
||
->select(['id', 'name'])
|
||
->select('test')
|
||
->build();
|
||
|
||
$this->assertEquals(
|
||
[
|
||
Selection::fromString('id'),
|
||
Selection::fromString('name'),
|
||
Selection::fromString('test'),
|
||
],
|
||
$select->getSelect()
|
||
);
|
||
}
|
||
|
||
public function testSelect2()
|
||
{
|
||
$select = $this->builder
|
||
->from('Test')
|
||
->select('test')
|
||
->select(['id', 'name'])
|
||
->build();
|
||
|
||
$this->assertEquals(
|
||
[
|
||
Selection::fromString('id'),
|
||
Selection::fromString('name'),
|
||
],
|
||
$select->getSelect()
|
||
);
|
||
}
|
||
|
||
public function testSelect3()
|
||
{
|
||
$select = $this->builder
|
||
->from('Test')
|
||
->select('test', 'hello')
|
||
->build();
|
||
|
||
$this->assertEquals(
|
||
[
|
||
Selection::fromString('test')->withAlias('hello'),
|
||
],
|
||
$select->getSelect()
|
||
);
|
||
}
|
||
|
||
public function testSelect4()
|
||
{
|
||
$select = $this->builder
|
||
->from('Test')
|
||
->select(Expr::create('test'), 'hello')
|
||
->build();
|
||
|
||
$this->assertEquals(
|
||
[
|
||
Selection::fromString('test')->withAlias('hello'),
|
||
],
|
||
$select->getSelect()
|
||
);
|
||
}
|
||
|
||
public function testSelect5()
|
||
{
|
||
$select = $this->builder
|
||
->from('Test')
|
||
->select('test')
|
||
->select([Expr::create('id'), Expr::create('name')])
|
||
->build();
|
||
|
||
$this->assertEquals(
|
||
[
|
||
Selection::fromString('id'),
|
||
Selection::fromString('name'),
|
||
],
|
||
$select->getSelect()
|
||
);
|
||
}
|
||
|
||
public function testSelect6()
|
||
{
|
||
$select = $this->builder
|
||
->from('Test')
|
||
->select('test')
|
||
->select([
|
||
[Expr::create('id'), 'id'],
|
||
[Expr::create('name'), 'name'],
|
||
])
|
||
->build();
|
||
|
||
$this->assertEquals(
|
||
[
|
||
Selection::fromString('id')->withAlias('id'),
|
||
Selection::fromString('name')->withAlias('name'),
|
||
],
|
||
$select->getSelect()
|
||
);
|
||
}
|
||
|
||
public function testSelect7()
|
||
{
|
||
$select = $this->builder
|
||
->from('Test')
|
||
->select([
|
||
'id',
|
||
Selection::create(Expr::create('name'))
|
||
])
|
||
->select(Selection::fromString('test')->withAlias('testAlias'))
|
||
->build();
|
||
|
||
$this->assertEquals(
|
||
[
|
||
Selection::fromString('id'),
|
||
Selection::fromString('name'),
|
||
Selection::fromString('test')->withAlias('testAlias'),
|
||
],
|
||
$select->getSelect()
|
||
);
|
||
}
|
||
|
||
public function testCloneNotSame()
|
||
{
|
||
$builder = new SelectBuilder();
|
||
|
||
$select = $builder
|
||
->from('Test')
|
||
->build();
|
||
|
||
$builder = new SelectBuilder();
|
||
|
||
$selectCloned = $builder
|
||
->clone($select)
|
||
->build();
|
||
|
||
$this->assertNotSame($selectCloned, $select);
|
||
}
|
||
|
||
public function testWhereNull1()
|
||
{
|
||
$select = $this->builder
|
||
->from('Test')
|
||
->where(['test' => null])
|
||
->build();
|
||
|
||
$raw = $select->getRaw();
|
||
|
||
$this->assertEquals(['test' => null], $raw['whereClause']);
|
||
}
|
||
|
||
public function testWhereNull2()
|
||
{
|
||
$select = $this->builder
|
||
->from('Test')
|
||
->where('test', null)
|
||
->build();
|
||
|
||
$raw = $select->getRaw();
|
||
|
||
$this->assertEquals(['test' => null], $raw['whereClause']);
|
||
}
|
||
|
||
public function testGroupBy1()
|
||
{
|
||
$select = $this->builder
|
||
->from('Test')
|
||
->having(['test' => null])
|
||
->group(['test'])
|
||
->build();
|
||
|
||
$raw = $select->getRaw();
|
||
|
||
$this->assertEquals(['test' => null], $raw['havingClause']);
|
||
|
||
$this->assertEquals(['test'], $raw['groupBy']);
|
||
}
|
||
|
||
public function testGroupBy2()
|
||
{
|
||
$select = $this->builder
|
||
->from('Test')
|
||
->having(Cond::equal(Expr::column('test'), null))
|
||
->group(Expr::create('test'))
|
||
->build();
|
||
|
||
$raw = $select->getRaw();
|
||
|
||
$this->assertEquals(['test'], $raw['groupBy']);
|
||
}
|
||
|
||
public function testGroupBy3()
|
||
{
|
||
$select = $this->builder
|
||
->from('Test')
|
||
->group([
|
||
Expr::create('test1'),
|
||
Expr::create('test2'),
|
||
])
|
||
->build();
|
||
|
||
|
||
$this->assertEquals(
|
||
[
|
||
Expr::create('test1'),
|
||
Expr::create('test2'),
|
||
],
|
||
$select->getGroup()
|
||
);
|
||
}
|
||
|
||
public function testHaving1()
|
||
{
|
||
$select = $this->builder
|
||
->from('Test')
|
||
->having(Cond::equal(Expr::column('test'), null))
|
||
->group(Expr::create('test'))
|
||
->build();
|
||
|
||
|
||
$this->assertEquals(
|
||
Cond::equal(Expr::column('test'), null)->getRaw(),
|
||
$select->getHaving()->getRaw()
|
||
);
|
||
}
|
||
|
||
public function testOrder1()
|
||
{
|
||
$select = $this->builder
|
||
->from('Test')
|
||
->order(Expr::create('test'))
|
||
->build();
|
||
|
||
$raw = $select->getRaw();
|
||
|
||
$this->assertEquals([['test', 'ASC']], $raw['orderBy']);
|
||
}
|
||
|
||
public function testOrder2()
|
||
{
|
||
$select = $this->builder
|
||
->from('Test')
|
||
->order(Expr::create('test'))
|
||
->build();
|
||
|
||
$raw = $select->getRaw();
|
||
|
||
$this->assertEquals([['test', 'ASC']], $raw['orderBy']);
|
||
}
|
||
|
||
public function testOrder3()
|
||
{
|
||
$select = $this->builder
|
||
->from('Test')
|
||
->order([Expr::create('test')], 'DESC')
|
||
->build();
|
||
|
||
$raw = $select->getRaw();
|
||
|
||
$this->assertEquals([['test', 'DESC']], $raw['orderBy']);
|
||
}
|
||
|
||
public function testOrder4()
|
||
{
|
||
$select = $this->builder
|
||
->from('Test')
|
||
->order([[Expr::create('test'), 'DESC']], 'ASC')
|
||
->build();
|
||
|
||
$raw = $select->getRaw();
|
||
|
||
$this->assertEquals([['test', 'DESC']], $raw['orderBy']);
|
||
}
|
||
|
||
public function testOrder5()
|
||
{
|
||
$select = $this->builder
|
||
->from('Test')
|
||
->order(Order::fromString('test')->withDesc())
|
||
->order('hello', true)
|
||
->build();
|
||
|
||
$this->assertEquals(
|
||
[
|
||
Order::fromString('test')->withDesc(),
|
||
Order::fromString('hello')->withDesc(),
|
||
],
|
||
$select->getOrder()
|
||
);
|
||
}
|
||
|
||
public function testOrder6()
|
||
{
|
||
$select = $this->builder
|
||
->from('Test')
|
||
->order([
|
||
Order::fromString('test')->withDesc(),
|
||
['hello', true],
|
||
])
|
||
->build();
|
||
|
||
$this->assertEquals(
|
||
[
|
||
Order::fromString('test')->withDesc(),
|
||
Order::fromString('hello')->withDesc(),
|
||
],
|
||
$select->getOrder()
|
||
);
|
||
}
|
||
|
||
public function testClone()
|
||
{
|
||
$builder = new SelectBuilder();
|
||
|
||
$select = $builder
|
||
->from('Test')
|
||
->where('test1', '1')
|
||
->build();
|
||
|
||
$builder = new SelectBuilder();
|
||
|
||
$selectCloned = $builder
|
||
->clone($select)
|
||
->distinct()
|
||
->where('test2', '2')
|
||
->build();
|
||
|
||
$params = $select->getRaw();
|
||
$paramsCloned = $selectCloned->getRaw();
|
||
|
||
$this->assertTrue($paramsCloned['distinct']);
|
||
$this->assertFalse($params['distinct'] ?? false);
|
||
|
||
$this->assertEquals(['test1' =>'1'], $params['whereClause']);
|
||
$this->assertEquals(['test1' => '1', 'test2' => '2'], $paramsCloned['whereClause']);
|
||
}
|
||
|
||
public function testCloneException()
|
||
{
|
||
$builder = new SelectBuilder();
|
||
|
||
$select = $builder
|
||
->from('Test')
|
||
->where('test1', '1')
|
||
->build();
|
||
|
||
$builder = new SelectBuilder();
|
||
|
||
$this->expectException(RuntimeException::class);
|
||
|
||
$builder
|
||
->from('Test')
|
||
->clone($select);
|
||
}
|
||
|
||
public function testWhereSameKeys1()
|
||
{
|
||
$builder = new SelectBuilder();
|
||
|
||
$select = $builder
|
||
->from('Test')
|
||
->where(['test' => '1'])
|
||
->where(['test' => '2'])
|
||
->build();
|
||
|
||
$raw = $select->getRaw();
|
||
|
||
$expected = [
|
||
'test' => '1',
|
||
['test' => '2'],
|
||
];
|
||
|
||
$this->assertEquals($expected, $raw['whereClause']);
|
||
}
|
||
|
||
public function testWhereSameKeys2()
|
||
{
|
||
$builder = new SelectBuilder();
|
||
|
||
$select = $builder
|
||
->from('Test')
|
||
->where([
|
||
'OR' => [
|
||
'test' => '1'
|
||
],
|
||
])
|
||
->where([
|
||
'OR' => [
|
||
'test' => '2'
|
||
],
|
||
])
|
||
->build();
|
||
|
||
$raw = $select->getRaw();
|
||
|
||
$expected = [
|
||
'OR' => [
|
||
'test' => '1'
|
||
],
|
||
[
|
||
'OR' => [
|
||
'test' => '2'
|
||
],
|
||
]
|
||
];
|
||
|
||
$this->assertEquals($expected, $raw['whereClause']);
|
||
}
|
||
|
||
public function testLeftJoin1()
|
||
{
|
||
$params = $this->builder
|
||
->from('Test')
|
||
->leftJoin('link1')
|
||
->leftJoin('link1')
|
||
->leftJoin('link2')
|
||
->build();
|
||
|
||
$this->assertEquals(
|
||
[
|
||
Join::create('link1')
|
||
->withLeft(),
|
||
Join::create('link2')
|
||
->withLeft()
|
||
],
|
||
$params->getJoins()
|
||
);
|
||
}
|
||
|
||
public function testLeftJoin2()
|
||
{
|
||
$query = $this->builder
|
||
->from('Test')
|
||
->leftJoin('link1', 'alias1', ['name' => 'test'])
|
||
->leftJoin('link2', 'alias2')
|
||
->build();
|
||
|
||
$this->assertEquals(
|
||
[
|
||
Join::create('link1', 'alias1')
|
||
->withConditions(WhereClause::fromRaw(['name' => 'test']))
|
||
->withLeft(),
|
||
Join::create('link2', 'alias2')
|
||
->withLeft(),
|
||
],
|
||
$query->getJoins()
|
||
);
|
||
}
|
||
|
||
public function testLeftJoin3()
|
||
{
|
||
$query = $this->builder
|
||
->from('Test')
|
||
->leftJoin(
|
||
Join::create('link1', 'alias1')
|
||
->withConditions(
|
||
WhereClause::fromRaw(['name' => 'test'])
|
||
)
|
||
)
|
||
->leftJoin(
|
||
Join::create('link2', 'alias2')
|
||
)
|
||
->build();
|
||
|
||
$this->assertEquals(
|
||
[
|
||
Join::create('link1', 'alias1')
|
||
->withConditions(WhereClause::fromRaw(['name' => 'test']))
|
||
->withLeft(),
|
||
Join::create('link2', 'alias2')
|
||
->withLeft(),
|
||
],
|
||
$query->getJoins()
|
||
);
|
||
}
|
||
|
||
public function testJoin1()
|
||
{
|
||
$params = $this->builder
|
||
->from('Test')
|
||
->join('link1')
|
||
->join('link1')
|
||
->join('link2')
|
||
->build()
|
||
->getRaw();
|
||
|
||
$this->assertEquals(
|
||
[
|
||
['link1', null, null, ['type' => null]],
|
||
['link2', null, null, ['type' => null]],
|
||
],
|
||
$params['joins']
|
||
);
|
||
}
|
||
|
||
public function testJoin2()
|
||
{
|
||
$query = $this->builder
|
||
->from('Test')
|
||
->join('link1', 'alias1', ['name' => 'test'])
|
||
->join('link2', 'alias2')
|
||
->build();
|
||
|
||
$this->assertEquals(
|
||
[
|
||
Join::create('link1', 'alias1')
|
||
->withConditions(WhereClause::fromRaw(['name' => 'test']))
|
||
->withInner(),
|
||
Join::create('link2', 'alias2')
|
||
->withInner(),
|
||
],
|
||
$query->getJoins()
|
||
);
|
||
}
|
||
|
||
public function testJoin3()
|
||
{
|
||
$query = $this->builder
|
||
->from('Test')
|
||
->join(
|
||
Join::create('link1', 'alias1')
|
||
->withConditions(WhereClause::fromRaw(['name' => 'test']))
|
||
)
|
||
->join(Join::create('link2', 'alias2'))
|
||
->build();
|
||
|
||
$this->assertEquals(
|
||
[
|
||
Join::create('link1', 'alias1')
|
||
->withConditions(WhereClause::fromRaw(['name' => 'test']))
|
||
->withInner(),
|
||
Join::create('link2', 'alias2')
|
||
->withInner(),
|
||
],
|
||
$query->getJoins()
|
||
);
|
||
}
|
||
|
||
public function testWhereItemUsage1()
|
||
{
|
||
$query = $this->builder
|
||
->from('Test')
|
||
->where(
|
||
Cond::or(
|
||
Cond::equal(Expr::column('test'), '1'),
|
||
Cond::equal(Expr::column('test'), '2')
|
||
)
|
||
)
|
||
->join(
|
||
'Table1',
|
||
'table1',
|
||
Cond::equal(Expr::column('table1.testId'), Expr::column('id'))
|
||
)
|
||
->leftJoin(
|
||
'Table2',
|
||
'table2',
|
||
Cond::equal(Expr::column('table2.testId'), Expr::column('id'))
|
||
)
|
||
->build();
|
||
|
||
$raw = $query->getRaw();
|
||
|
||
$expectedWhere = [
|
||
'OR' => [
|
||
['test=' => '1'],
|
||
['test=' => '2'],
|
||
]
|
||
];
|
||
|
||
$expectedJoins = [
|
||
[
|
||
'Table1',
|
||
'table1',
|
||
[
|
||
'table1.testId=:' => 'id'
|
||
],
|
||
['noLeftAlias' => true, 'type' => null]
|
||
],
|
||
[
|
||
'Table2',
|
||
'table2',
|
||
[
|
||
'table2.testId=:' => 'id'
|
||
],
|
||
['noLeftAlias' => true, 'type' => Join\JoinType::left]
|
||
]
|
||
];
|
||
|
||
$this->assertEquals($expectedWhere, $raw['whereClause']);
|
||
$this->assertEquals($expectedJoins, $raw['joins']);
|
||
}
|
||
|
||
public function testExists1(): void
|
||
{
|
||
$query = (new SelectBuilder())
|
||
->select('id')
|
||
->from('Test', 'test')
|
||
->where(
|
||
Cond::exists(
|
||
(new SelectBuilder())
|
||
->select('id')
|
||
->from('Test', 'sq')
|
||
->where(
|
||
Cond::equal(
|
||
Cond::column('test.id'),
|
||
Cond::column('sq.id')
|
||
)
|
||
)
|
||
->build()
|
||
)
|
||
)
|
||
->build();
|
||
|
||
$expected = [
|
||
'select' => ['id'],
|
||
'from' => 'Test',
|
||
'fromAlias' => 'test',
|
||
'whereClause' => [
|
||
'EXISTS' => (new SelectBuilder())
|
||
->select('id')
|
||
->from('Test', 'sq')
|
||
->where(
|
||
Cond::equal(
|
||
Cond::column('test.id'),
|
||
Cond::column('sq.id')
|
||
)
|
||
)
|
||
->build()
|
||
],
|
||
];
|
||
|
||
$this->assertEquals($expected, $query->getRaw());
|
||
}
|
||
}
|