<?php

declare(strict_types=1);

/**

* This file is part of Hyperf.

*

* @link https://www.hyperf.io

* @document https://hyperf.wiki

* @contact group@hyperf.io

* @license https://github.com/hyperf/hyperf/blob/master/LICENSE

*/

namespace HyperfTest\Database;

use Hyperf\Collection\Collection;

use Hyperf\Context\ApplicationContext;

use Hyperf\Database\Connection;

use Hyperf\Database\ConnectionInterface;

use Hyperf\Database\Exception\InvalidBindingException;

use Hyperf\Database\Query\Builder;

use Hyperf\Database\Query\Expression as Raw;

use Hyperf\Database\Query\Grammars\Grammar;

use Hyperf\Database\Query\Grammars\MySqlGrammar;

use Hyperf\Database\Query\Processors\MySqlProcessor;

use Hyperf\Database\Query\Processors\Processor;

use Hyperf\Paginator\Cursor;

use Hyperf\Paginator\CursorPaginator;

use Hyperf\Paginator\Paginator;

use InvalidArgumentException;

use Mockery as m;

use Mockery\MockInterface;

use PHPUnit\Framework\Attributes\CoversNothing;

use PHPUnit\Framework\TestCase;

use Psr\Container\ContainerInterface;

use RuntimeException;

use TypeError;

use function Hyperf\Collection\collect;

use function Hyperf\Support\now;

/**

* @internal

* @coversNothing

*/

#[CoversNothing]

class DatabaseQueryBuilderTest extends TestCase

{

protected function setUp(): void

{

parent::setUp();

ApplicationContext::setContainer(m::mock(ContainerInterface::class));

}

protected function tearDown(): void

{

m::close();

}

public function testWhereValueBetween()

{

$builder = $this->getBuilder();

$builder->select('*')->from('users')->whereValueBetween('2020-01-01 19:30:00', ['created_at', 'updated_at']);

$this->assertSame('select * from "users" where ? between "created_at" and "updated_at"', $builder->toSql());

$this->assertEquals([0 => '2020-01-01 19:30:00'], $builder->getBindings());

$builder = $this->getBuilder();

$builder->select('*')->from('users')->whereValueBetween('2020-01-01 19:30:00', ['created_at', 'updated_at']);

$this->assertSame('select * from "users" where ? between "created_at" and "updated_at"', $builder->toSql());

$this->assertEquals([0 => '2020-01-01 19:30:00'], $builder->getBindings());

$builder = $this->getBuilder();

$builder->select('*')->from('users')->whereValueBetween('2020-01-01 19:30:00', [new Raw(1), new Raw(2)]);

$this->assertSame('select * from "users" where ? between 1 and 2', $builder->toSql());

$this->assertEquals([0 => '2020-01-01 19:30:00'], $builder->getBindings());

$builder = $this->getBuilder();

$builder->select('*')->from('users')->whereValueBetween(new Raw(1), ['created_at', 'updated_at']);

$this->assertSame('select * from "users" where 1 between "created_at" and "updated_at"', $builder->toSql());

}

public function testOrWhereValueBetween()

{

$builder = $this->getBuilder();

$builder->select('*')->from('users')->where('id', 2)->orWhereValueBetween('2020-01-01 19:30:00', ['created_at', 'updated_at']);

$this->assertSame('select * from "users" where "id" = ? or ? between "created_at" and "updated_at"', $builder->toSql());

$this->assertEquals([0 => 2, 1 => '2020-01-01 19:30:00'], $builder->getBindings());

$builder = $this->getBuilder();

$builder->select('*')->from('users')->where('id', 2)->orWhereValueBetween('2020-01-01 19:30:00', ['created_at', 'updated_at']);

$this->assertSame('select * from "users" where "id" = ? or ? between "created_at" and "updated_at"', $builder->toSql());

$this->assertEquals([0 => 2, 1 => '2020-01-01 19:30:00'], $builder->getBindings());

$builder = $this->getBuilder();

$builder->select('*')->from('users')->where('id', 2)->orWhereValueBetween('2020-01-01 19:30:00', [new Raw(1), new Raw(2)]);

$this->assertSame('select * from "users" where "id" = ? or ? between 1 and 2', $builder->toSql());

$this->assertEquals([0 => 2, 1 => '2020-01-01 19:30:00'], $builder->getBindings());

$builder = $this->getBuilder();

$builder->select('*')->from('users')->where('id', 2)->orWhereValueBetween(new Raw(1), ['created_at', 'updated_at']);

$this->assertSame('select * from "users" where "id" = ? or 1 between "created_at" and "updated_at"', $builder->toSql());

}

public function testWhereValueNotBetween()

{

$builder = $this->getBuilder();

$builder->select('*')->from('users')->whereValueNotBetween('2020-01-01 19:30:00', ['created_at', 'updated_at']);

$this->assertSame('select * from "users" where ? not between "created_at" and "updated_at"', $builder->toSql());

$this->assertEquals([0 => '2020-01-01 19:30:00'], $builder->getBindings());

$builder = $this->getBuilder();

$builder->select('*')->from('users')->whereValueNotBetween('2020-01-01 19:30:00', ['created_at', 'updated_at']);

$this->assertSame('select * from "users" where ? not between "created_at" and "updated_at"', $builder->toSql());

$this->assertEquals([0 => '2020-01-01 19:30:00'], $builder->getBindings());

$builder = $this->getBuilder();

$builder->select('*')->from('users')->whereValueNotBetween('2020-01-01 19:30:00', [new Raw(1), new Raw(2)]);

$this->assertSame('select * from "users" where ? not between 1 and 2', $builder->toSql());

$this->assertEquals([0 => '2020-01-01 19:30:00'], $builder->getBindings());

$builder = $this->getBuilder();

$builder->select('*')->from('users')->whereValueNotBetween(new Raw(1), ['created_at', 'updated_at']);

$this->assertSame('select * from "users" where 1 not between "created_at" and "updated_at"', $builder->toSql());

}

public function testOrWhereValueNotBetween()

{

$builder = $this->getBuilder();

$builder->select('*')->from('users')->where('id', 2)->orWhereValueNotBetween('2020-01-01 19:30:00', ['created_at', 'updated_at']);

$this->assertSame('select * from "users" where "id" = ? or ? not between "created_at" and "updated_at"', $builder->toSql());

$this->assertEquals([0 => 2, 1 => '2020-01-01 19:30:00'], $builder->getBindings());

$builder = $this->getBuilder();

$builder->select('*')->from('users')->where('id', 2)->orWhereValueNotBetween('2020-01-01 19:30:00', ['created_at', 'updated_at']);

$this->assertSame('select * from "users" where "id" = ? or ? not between "created_at" and "updated_at"', $builder->toSql());

$this->assertEquals([0 => 2, 1 => '2020-01-01 19:30:00'], $builder->getBindings());

$builder = $this->getBuilder();

$builder->select('*')->from('users')->where('id', 2)->orWhereValueNotBetween('2020-01-01 19:30:00', [new Raw(1), new Raw(2)]);

$this->assertSame('select * from "users" where "id" = ? or ? not between 1 and 2', $builder->toSql());

$this->assertEquals([0 => 2, 1 => '2020-01-01 19:30:00'], $builder->getBindings());

$builder = $this->getBuilder();

$builder->select('*')->from('users')->where('id', 2)->orWhereValueNotBetween(new Raw(1), ['created_at', 'updated_at']);

$this->assertSame('select * from "users" where "id" = ? or 1 not between "created_at" and "updated_at"', $builder->toSql());

}

public function testBasicSelect(): void

{

$builder = $this->getBuilder();

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

$this->assertSame('select * from "users"', $builder->toSql());

}

public function testBasicSelectWithGetColumns(): void

{

$builder = $this->getBuilder();

$builder->getProcessor()->shouldReceive('processSelect');

$builder->getConnection()->shouldReceive('select')->once()->andReturnUsing(function ($sql) {

$this->assertSame('select * from "users"', $sql);

return [];

});

$builder->getConnection()->shouldReceive('select')->once()->andReturnUsing(function ($sql) {

$this->assertSame('select "foo", "bar" from "users"', $sql);

return [];

});

$builder->getConnection()->shouldReceive('select')->once()->andReturnUsing(function ($sql) {

$this->assertSame('select "baz" from "users"', $sql);

return [];

});

$builder->from('users')->get();

$this->assertNull($builder->columns);

$builder->from('users')->get(['foo', 'bar']);

$this->assertNull($builder->columns);

$builder->from('users')->get('baz');

$this->assertNull($builder->columns);

$this->assertSame('select * from "users"', $builder->toSql());

$this->assertNull($builder->columns);

}

public function testBasicSelectUseWritePdo(): void

{

$builder = $this->getMySqlBuilderWithProcessor();

$builder->getConnection()->shouldReceive('select')->once()

->with('select * from `users`', [], false);

$builder->useWritePdo()->select('*')->from('users')->get();

$builder = $this->getMySqlBuilderWithProcessor();

$builder->getConnection()->shouldReceive('select')->once()

->with('select * from `users`', [], true);

$builder->select('*')->from('users')->get();

$this->assertTrue(true);

}

public function testBasicTableWrappingProtectsQuotationMarks(): void

{

$builder = $this->getBuilder();

$builder->select('*')->from('some"table');

$this->assertSame('select * from "some""table"', $builder->toSql());

}

public function testAliasWrappingAsWholeConstant(): void

{

$builder = $this->getBuilder();

$builder->select('x.y as foo.bar')->from('baz');

$this->assertSame('select "x"."y" as "foo.bar" from "baz"', $builder->toSql());

}

public function testAliasWrappingWithSpacesInDatabaseName(): void

{

$builder = $this->getBuilder();

$builder->select('w x.y.z as foo.bar')->from('baz');

$this->assertSame('select "w x"."y"."z" as "foo.bar" from "baz"', $builder->toSql());

}

public function testAddingSelects(): void

{

$builder = $this->getBuilder();

$builder->select('foo')->addSelect('bar')->addSelect(['baz', 'boom'])->from('users');

$this->assertSame('select "foo", "bar", "baz", "boom" from "users"', $builder->toSql());

}

public function testBasicSelectWithPrefix(): void

{

$builder = $this->getBuilder();

$builder->getGrammar()->setTablePrefix('prefix_');

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

$this->assertSame('select * from "prefix_users"', $builder->toSql());

}

public function testUseIndex(): void

{

$builder = $this->getMySqlBuilder();

$builder->select('*')->from('users')->useIndex('index1');

$this->assertSame('select * from `users` use index (index1)', $builder->toSql());

$builder->select('*')->from('users')->useIndex('index2');

$this->assertSame('select * from `users` use index (index2)', $builder->toSql());

$builder->select('*')->from('users')->useIndex('index1,index2');

$this->assertSame('select * from `users` use index (index1,index2)', $builder->toSql());

$builder = $this->getMySqlBuilder()->select('*')->from('users');

$this->assertSame('select * from `users`', $builder->toSql());

}

public function testForceIndex(): void

{

$builder = $this->getMySqlBuilder();

$builder1 = (clone $builder)->select('*')->from('users')->where('username', 'xxx')->forceIndex('index1');

$foeceses = (clone $builder)->select('*')->from('users')->where('username', 'xxx')->forceIndexes(['index1']);

$this->assertSame('select * from `users` force index (index1) where `username` = ?', $builder1->toSql());

$this->assertSame('select * from `users` force index (`index1`) where `username` = ?', $foeceses->toSql());

$builder2 = (clone $builder);

$builder2->select('*')->from('users')->where('username', 'xxx')->forceIndex('index2');

$foeceses = (clone $builder);

$foeceses->select('*')->from('users')->where('username', 'xxx')->forceIndexes(['index2']);

$this->assertSame('select * from `users` force index (index2) where `username` = ?', $builder2->toSql());

$this->assertSame('select * from `users` force index (`index2`) where `username` = ?', $foeceses->toSql());

$builder3 = (clone $builder);

$foeceses = (clone $builder);

$builder3->select('*')->from('users')->where('username', 'xxx')->forceIndex('index1,index2');

$foeceses->select('*')->from('users')->where('username', 'xxx')->forceIndexes(['index1', 'index2']);

$this->assertSame('select * from `users` force index (index1,index2) where `username` = ?', $builder3->toSql());

$this->assertSame('select * from `users` force index (`index1`,`index2`) where `username` = ?', $foeceses->toSql());

}

public function testIgnoreIndex(): void

{

$builder = $this->getMySqlBuilder();

$builder->select('*')->from('users')->ignoreIndex('index1');

$this->assertSame('select * from `users` ignore index (index1)', $builder->toSql());

$builder->select('*')->from('users')->ignoreIndex('index2');

$this->assertSame('select * from `users` ignore index (index2)', $builder->toSql());

$builder->select('*')->from('users')->ignoreIndex('index1,index2');

$this->assertSame('select * from `users` ignore index (index1,index2)', $builder->toSql());

}

public function testBasicSelectDistinct(): void

{

$builder = $this->getBuilder();

$builder->distinct()->select('foo', 'bar')->from('users');

$this->assertSame('select distinct "foo", "bar" from "users"', $builder->toSql());

}

public function testBasicSelectDistinctOnColumns(): void

{

$builder = $this->getBuilder();

$builder->distinct('foo')->select('foo', 'bar')->from('users');

$this->assertSame('select distinct "foo", "bar" from "users"', $builder->toSql());

}

public function testBasicAlias(): void

{

$builder = $this->getBuilder();

$builder->select('foo as bar')->from('users');

$this->assertSame('select "foo" as "bar" from "users"', $builder->toSql());

}

public function testAliasWithPrefix(): void

{

$builder = $this->getBuilder();

$builder->getGrammar()->setTablePrefix('prefix_');

$builder->select('*')->from('users as people');

$this->assertSame('select * from "prefix_users" as "prefix_people"', $builder->toSql());

}

public function testJoinAliasesWithPrefix(): void

{

$builder = $this->getBuilder();

$builder->getGrammar()->setTablePrefix('prefix_');

$builder->select('*')->from('services')->join('translations AS t', 't.item_id', '=', 'services.id');

$this->assertSame('select * from "prefix_services" inner join "prefix_translations" as "prefix_t" on "prefix_t"."item_id" = "prefix_services"."id"', $builder->toSql());

}

public function testBasicTableWrapping(): void

{

$builder = $this->getBuilder();

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

$this->assertSame('select * from "public"."users"', $builder->toSql());

}

public function testWhenCallback(): void

{

$callback = function ($query, $condition) {

$this->assertTrue($condition);

$query->where('id', '=', 1);

};

$builder = $this->getBuilder();

$builder->select('*')->from('users')->when(true, $callback)->where('email', 'foo');

$this->assertSame('select * from "users" where "id" = ? and "email" = ?', $builder->toSql());

$builder = $this->getBuilder();

$builder->select('*')->from('users')->when(false, $callback)->where('email', 'foo');

$this->assertSame('select * from "users" where "email" = ?', $builder->toSql());

}

public function testWhenValueOfCallback(): void

{

$callback = function (Builder $query, $condition) {

$this->assertTrue($condition);

$query->where('id', '=', 1);

};

$builder = $this->getBuilder();

$builder->select('*')

->from('users')

->when(fn (Builder $query) => true, $callback)

->where('email', 'foo');

$this->assertSame('select * from "users" where "id" = ? and "email" = ?', $builder->toSql());

$builder = $this->getBuilder();

$builder->select('*')

->from('users')

->when(fn (Builder $query) => false, $callback)

->where('email', 'foo');

$this->assertSame('select * from "users" where "email" = ?', $builder->toSql());

}

public function testWhenCallbackWithReturn(): void

{

$callback = function ($query, $condition) {

$this->assertTrue($condition);

return $query->where('id', '=', 1);

};

$builder = $this->getBuilder();

$builder->select('*')->from('users')->when(true, $callback)->where('email', 'foo');

$this->assertSame('select * from "users" where "id" = ? and "email" = ?', $builder->toSql());

$builder = $this->getBuilder();

$builder->select('*')->from('users')->when(false, $callback)->where('email', 'foo');

$this->assertSame('select * from "users" where "email" = ?', $builder->toSql());

}

public function testWhenCallbackWithDefault(): void

{

$callback = function ($query, $condition) {

$this->assertEquals($condition, 'truthy');

$query->where('id', '=', 1);

};

$default = function ($query, $condition) {

$this->assertEquals($condition, 0);

$query->where('id', '=', 2);

};

$builder = $this->getBuilder();

$builder->select('*')->from('users')->when('truthy', $callback, $default)->where('email', 'foo');

$this->assertSame('select * from "users" where "id" = ? and "email" = ?', $builder->toSql());

$this->assertEquals([0 => 1, 1 => 'foo'], $builder->getBindings());

$builder = $this->getBuilder();

$builder->select('*')->from('users')->when(0, $callback, $default)->where('email', 'foo');

$this->assertSame('select * from "users" where "id" = ? and "email" = ?', $builder->toSql());

$this->assertEquals([0 => 2, 1 => 'foo'], $builder->getBindings());

}

public function testUnlessCallback(): void

{

$callback = function ($query, $condition) {

$this->assertFalse($condition);

$query->where('id', '=', 1);

};

$builder = $this->getBuilder();

$builder->select('*')->from('users')->unless(false, $callback)->where('email', 'foo');

$this->assertSame('select * from "users" where "id" = ? and "email" = ?', $builder->toSql());

$builder = $this->getBuilder();

$builder->select('*')->from('users')->unless(true, $callback)->where('email', 'foo');

$this->assertSame('select * from "users" where "email" = ?', $builder->toSql());

}

public function testUnlessValueOfCallback(): void

{

$callback = function (Builder $query, $condition) {

$this->assertFalse($condition);

$query->where('id', '=', 1);

};

$builder = $this->getBuilder();

$builder->select('*')

->from('users')

->unless(fn (Builder $query) => true, $callback)

->where('email', 'foo');

$this->assertSame('select * from "users" where "email" = ?', $builder->toSql());

$builder = $this->getBuilder();

$builder->select('*')

->from('users')

->unless(fn (Builder $query) => false, $callback)

->where('email', 'foo');

$this->assertSame('select * from "users" where "id" = ? and "email" = ?', $builder->toSql());

}

public function testUnlessCallbackWithReturn(): void

{

$callback = function ($query, $condition) {

$this->assertFalse($condition);

return $query->where('id', '=', 1);

};

$builder = $this->getBuilder();

$builder->select('*')->from('users')->unless(false, $callback)->where('email', 'foo');

$this->assertSame('select * from "users" where "id" = ? and "email" = ?', $builder->toSql());

$builder = $this->getBuilder();

$builder->select('*')->from('users')->unless(true, $callback)->where('email', 'foo');

$this->assertSame('select * from "users" where "email" = ?', $builder->toSql());

}

public function testUnlessCallbackWithDefault(): void

{

$callback = function ($query, $condition) {

$this->assertEquals($condition, 0);

$query->where('id', '=', 1);

};

$default = function ($query, $condition) {

$this->assertEquals($condition, 'truthy');

$query->where('id', '=', 2);

};

$builder = $this->getBuilder();

$builder->select('*')->from('users')->unless(0, $callback, $default)->where('email', 'foo');

$this->assertSame('select * from "users" where "id" = ? and "email" = ?', $builder->toSql());

$this->assertEquals([0 => 1, 1 => 'foo'], $builder->getBindings());

$builder = $this->getBuilder();

$builder->select('*')->from('users')->unless('truthy', $callback, $default)->where('email', 'foo');

$this->assertSame('select * from "users" where "id" = ? and "email" = ?', $builder->toSql());

$this->assertEquals([0 => 2, 1 => 'foo'], $builder->getBindings());

}

public function testTapCallback(): void

{

$callback = function ($query) {

return $query->where('id', '=', 1);

};

$builder = $this->getBuilder();

$builder->select('*')->from('users')->tap($callback)->where('email', 'foo');

$this->assertSame('select * from "users" where "id" = ? and "email" = ?', $builder->toSql());

}

public function testBasicWheres(): void

{

$builder = $this->getBuilder();

$builder->select('*')->from('users')->where('id', '=', 1);

$this->assertSame('select * from "users" where "id" = ?', $builder->toSql());

$this->assertEquals([0 => 1], $builder->getBindings());

}

public function testWhereWithArrayValue(): void

{

$builder = $this->getBuilder();

$builder->select('*')->from('users')->where('id', 12);

$this->assertSame('select * from "users" where "id" = ?', $builder->toSql());

$this->assertEquals([0 => 12], $builder->getBindings());

$this->expectException(InvalidBindingException::class);

$this->expectExceptionMessage('The value of column id is invalid.');

$builder = $this->getBuilder();

$builder->select('*')->from('users')->where('id', [12]);

}

public function testWhereBetweenWithArrayValue(): void

{

$builder = $this->getBuilder();

$builder->select('*')->from('users')->whereBetween('id', [1, 100]);

$this->assertSame('select * from "users" where "id" between ? and ?', $builder->toSql());

$this->assertEquals([0 => 1, 1 => 100], $builder->getBindings());

$this->expectException(InvalidBindingException::class);

$this->expectExceptionMessage('The value length of column id is not equal with 2.');

$builder = $this->getBuilder();

$builder->select('*')->from('users')->whereBetween('id', [1, 2, 3]);

}

public function testWhereBetweenWithoutArrayValue(): void

{

$this->expectException(TypeError::class);

$builder = $this->getBuilder();

$builder->select('*')->from('users')->whereBetween('id', 1);

}

public function testMySqlWrappingProtectsQuotationMarks(): void

{

$builder = $this->getMySqlBuilder();

$builder->select('*')->From('some`table');

$this->assertSame('select * from `some``table`', $builder->toSql());

}

public function testDateBasedWheresAcceptsTwoArguments(): void

{

$builder = $this->getMySqlBuilder();

$builder->select('*')->from('users')->whereDate('created_at', 1);

$this->assertSame('select * from `users` where date(`created_at`) = ?', $builder->toSql());

$builder = $this->getMySqlBuilder();

$builder->select('*')->from('users')->whereDay('created_at', 1);

$this->assertSame('select * from `users` where day(`created_at`) = ?', $builder->toSql());

$builder = $this->getMySqlBuilder();

$builder->select('*')->from('users')->whereMonth('created_at', 1);

$this->assertSame('select * from `users` where month(`created_at`) = ?', $builder->toSql());

$builder = $this->getMySqlBuilder();

$builder->select('*')->from('users')->whereYear('created_at', 1);

$this->assertSame('select * from `users` where year(`created_at`) = ?', $builder->toSql());

}

public function testDateBasedOrWheresAcceptsTwoArguments(): void

{

$builder = $this->getMySqlBuilder();

$builder->select('*')->from('users')->where('id', 1)->orWhereDate('created_at', 1);

$this->assertSame('select * from `users` where `id` = ? or date(`created_at`) = ?', $builder->toSql());

$builder = $this->getMySqlBuilder();

$builder->select('*')->from('users')->where('id', 1)->orWhereDay('created_at', 1);

$this->assertSame('select * from `users` where `id` = ? or day(`created_at`) = ?', $builder->toSql());

$builder = $this->getMySqlBuilder();

$builder->select('*')->from('users')->where('id', 1)->orWhereMonth('created_at', 1);

$this->assertSame('select * from `users` where `id` = ? or month(`created_at`) = ?', $builder->toSql());

$builder = $this->getMySqlBuilder();

$builder->select('*')->from('users')->where('id', 1)->orWhereYear('created_at', 1);

$this->assertSame('select * from `users` where `id` = ? or year(`created_at`) = ?', $builder->toSql());

}

public function testDateBasedWheresExpressionIsNotBound(): void

{

$builder = $this->getBuilder();

$builder->select('*')->from('users')->whereDate('created_at', new Raw('NOW()'))->where('admin', true);

$this->assertEquals([true], $builder->getBindings());

$builder = $this->getBuilder();

$builder->select('*')->from('users')->whereDay('created_at', new Raw('NOW()'));

$this->assertEquals([], $builder->getBindings());

$builder = $this->getBuilder();

$builder->select('*')->from('users')->whereMonth('created_at', new Raw('NOW()'));

$this->assertEquals([], $builder->getBindings());

$builder = $this->getBuilder();

$builder->select('*')->from('users')->whereYear('created_at', new Raw('NOW()'));

$this->assertEquals([], $builder->getBindings());

}

public function testWhereDateMySql(): void

{

$builder = $this->getMySqlBuilder();

$builder->select('*')->from('users')->whereDate('created_at', '=', '2015-12-21');

$this->assertSame('select * from `users` where date(`created_at`) = ?', $builder->toSql());

$this->assertEquals([0 => '2015-12-21'], $builder->getBindings());

$builder = $this->getMySqlBuilder();

$builder->select('*')->from('users')->whereDate('created_at', '=', new Raw('NOW()'));

$this->assertSame('select * from `users` where date(`created_at`) = NOW()', $builder->toSql());

}

public function testWhereDayMySql(): void

{

$builder = $this->getMySqlBuilder();

$builder->select('*')->from('users')->whereDay('created_at', '=', 1);

$this->assertSame('select * from `users` where day(`created_at`) = ?', $builder->toSql());

$this->assertEquals([0 => 1], $builder->getBindings());

}

public function testOrWhereDayMySql(): void

{

$builder = $this->getMySqlBuilder();

$builder->select('*')->from('users')->whereDay('created_at', '=', 1)->orWhereDay('created_at', '=', 2);

$this->assertSame('select * from `users` where day(`created_at`) = ? or day(`created_at`) = ?', $builder->toSql());

$this->assertEquals([0 => 1, 1 => 2], $builder->getBindings());

}

public function testWhereMonthMySql(): void

{

$builder = $this->getMySqlBuilder();

$builder->select('*')->from('users')->whereMonth('created_at', '=', 5);

$this->assertSame('select * from `users` where month(`created_at`) = ?', $builder->toSql());

$this->assertEquals([0 => 5], $builder->getBindings());

}

public function testOrWhereMonthMySql(): void

{

$builder = $this->getMySqlBuilder();

$builder->select('*')->from('users')->whereMonth('created_at', '=', 5)->orWhereMonth('created_at', '=', 6);

$this->assertSame('select * from `users` where month(`created_at`) = ? or month(`created_at`) = ?', $builder->toSql());

$this->assertEquals([0 => 5, 1 => 6], $builder->getBindings());

}

public function testWhereYearMySql(): void

{

$builder = $this->getMySqlBuilder();

$builder->select('*')->from('users')->whereYear('created_at', '=', 2014);

$this->assertSame('select * from `users` where year(`created_at`) = ?', $builder->toSql());

$this->assertEquals([0 => 2014], $builder->getBindings());

}

public function testOrWhereYearMySql(): void

{

$builder = $this->getMySqlBuilder();

$builder->select('*')->from('users')->whereYear('created_at', '=', 2014)->orWhereYear('created_at', '=', 2015);

$this->assertSame('select * from `users` where year(`created_at`) = ? or year(`created_at`) = ?', $builder->toSql());

$this->assertEquals([0 => 2014, 1 => 2015], $builder->getBindings());

}

public function testWhereTimeMySql(): void

{

$builder = $this->getMySqlBuilder();

$builder->select('*')->from('users')->whereTime('created_at', '>=', '22:00');

$this->assertSame('select * from `users` where time(`created_at`) >= ?', $builder->toSql());

$this->assertEquals([0 => '22:00'], $builder->getBindings());

}

public function testWhereTimeMySqlWithArrayValue(): void

{

$this->expectException(InvalidBindingException::class);

$this->expectExceptionMessage('The value of column created_at is invalid.');

$builder = $this->getMySqlBuilder();

$builder->select('*')->from('users')->whereTime('created_at', '>=', ['22:00', '10:00']);

}

public function testWhereTimeOperatorOptionalMySql(): void

{

$builder = $this->getMySqlBuilder();

$builder->select('*')->from('users')->whereTime('created_at', '22:00');

$this->assertSame('select * from `users` where time(`created_at`) = ?', $builder->toSql());

$this->assertEquals([0 => '22:00'], $builder->getBindings());

}

public function testWhereBetweens(): void

{

$builder = $this->getBuilder();

$builder->select('*')->from('users')->whereBetween('id', [1, 2]);

$this->assertSame('select * from "users" where "id" between ? and ?', $builder->toSql());

$this->assertEquals([0 => 1, 1 => 2], $builder->getBindings());

$builder = $this->getBuilder();

$builder->select('*')->from('users')->whereNotBetween('id', [1, 2]);

$this->assertSame('select * from "users" where "id" not between ? and ?', $builder->toSql());

$this->assertEquals([0 => 1, 1 => 2], $builder->getBindings());

$builder = $this->getBuilder();

$builder->select('*')->from('users')->whereBetween('id', [new Raw(1), new Raw(2)]);

$this->assertSame('select * from "users" where "id" between 1 and 2', $builder->toSql());

$this->assertEquals([], $builder->getBindings());

}

public function testBasicOrWheres(): void

{

$builder = $this->getBuilder();

$builder->select('*')->from('users')->where('id', '=', 1)->orWhere('email', '=', 'foo');

$this->assertSame('select * from "users" where "id" = ? or "email" = ?', $builder->toSql());

$this->assertEquals([0 => 1, 1 => 'foo'], $builder->getBindings());

}

public function testRawWheres(): void

{

$builder = $this->getBuilder();

$builder->select('*')->from('users')->whereRaw('id = ? or email = ?', [1, 'foo']);

$this->assertSame('select * from "users" where id = ? or email = ?', $builder->toSql());

$this->assertEquals([0 => 1, 1 => 'foo'], $builder->getBindings());

}

public function testRawOrWheres(): void

{

$builder = $this->getBuilder();

$builder->select('*')->from('users')->where('id', '=', 1)->orWhereRaw('email = ?', ['foo']);

$this->assertSame('select * from "users" where "id" = ? or email = ?', $builder->toSql());

$this->assertEquals([0 => 1, 1 => 'foo'], $builder->getBindings());

}

public function testBasicWhereIns(): void

{

$builder = $this->getBuilder();

$builder->select('*')->from('users')->whereIn('id', [1, 2, 3]);

$this->assertSame('select * from "users" where "id" in (?, ?, ?)', $builder->toSql());

$this->assertEquals([0 => 1, 1 => 2, 2 => 3], $builder->getBindings());

$builder = $this->getBuilder();

$builder->select('*')->from('users')->where('id', '=', 1)->orWhereIn('id', [1, 2, 3]);

$this->assertSame('select * from "users" where "id" = ? or "id" in (?, ?, ?)', $builder->toSql());

$this->assertEquals([0 => 1, 1 => 1, 2 => 2, 3 => 3], $builder->getBindings());

}

public function testBasicWhereNotIns(): void

{

$builder = $this->getBuilder();

$builder->select('*')->from('users')->whereNotIn('id', [1, 2, 3]);

$this->assertSame('select * from "users" where "id" not in (?, ?, ?)', $builder->toSql());

$this->assertEquals([0 => 1, 1 => 2, 2 => 3], $builder->getBindings());

$builder = $this->getBuilder();

$builder->select('*')->from('users')->where('id', '=', 1)->orWhereNotIn('id', [1, 2, 3]);

$this->assertSame('select * from "users" where "id" = ? or "id" not in (?, ?, ?)', $builder->toSql());

$this->assertEquals([0 => 1, 1 => 1, 2 => 2, 3 => 3], $builder->getBindings());

}

public function testRawWhereIns(): void

{

$builder = $this->getBuilder();

$builder->select('*')->from('users')->whereIn('id', [new Raw(1)]);

$this->assertSame('select * from "users" where "id" in (1)', $builder->toSql());

$builder = $this->getBuilder();

$builder->select('*')->from('users')->where('id', '=', 1)->orWhereIn('id', [new Raw(1)]);

$this->assertSame('select * from "users" where "id" = ? or "id" in (1)', $builder->toSql());

$this->assertEquals([0 => 1], $builder->getBindings());

}

public function testEmptyWhereIns(): void

{

$builder = $this->getBuilder();

$builder->select('*')->from('users')->whereIn('id', []);

$this->assertSame('select * from "users" where 0 = 1', $builder->toSql());

$this->assertEquals([], $builder->getBindings());

$builder = $this->getBuilder();

$builder->select('*')->from('users')->where('id', '=', 1)->orWhereIn('id', []);

$this->assertSame('select * from "users" where "id" = ? or 0 = 1', $builder->toSql());

$this->assertEquals([0 => 1], $builder->getBindings());

}

public function testEmptyWhereNotIns(): void

{

$builder = $this->getBuilder();

$builder->select('*')->from('users')->whereNotIn('id', []);

$this->assertSame('select * from "users" where 1 = 1', $builder->toSql());

$this->assertEquals([], $builder->getBindings());

$builder = $this->getBuilder();

$builder->select('*')->from('users')->where('id', '=', 1)->orWhereNotIn('id', []);

$this->assertSame('select * from "users" where "id" = ? or 1 = 1', $builder->toSql());

$this->assertEquals([0 => 1], $builder->getBindings());

}

public function testWhereIntegerInRaw(): void

{

$builder = $this->getBuilder();

$builder->select('*')->from('users')->whereIntegerInRaw('id', ['1a', 2]);

$this->assertSame('select * from "users" where "id" in (1, 2)', $builder->toSql());

$this->assertEquals([], $builder->getBindings());

}

public function testWhereIntegerNotInRaw(): void

{

$builder = $this->getBuilder();

$builder->select('*')->from('users')->whereIntegerNotInRaw('id', ['1a', 2]);

$this->assertSame('select * from "users" where "id" not in (1, 2)', $builder->toSql());

$this->assertEquals([], $builder->getBindings());

}

public function testEmptyWhereIntegerInRaw(): void

{

$builder = $this->getBuilder();

$builder->select('*')->from('users')->whereIntegerInRaw('id', []);

$this->assertSame('select * from "users" where 0 = 1', $builder->toSql());

$this->assertEquals([], $builder->getBindings());

}

public function testExplain(): void

{

$builder = $this->getBuilder();

/**

* @var ConnectionInterface|MockInterface $connection

*/

$connection = $builder->getConnection();

$connection->allows('select')

->once()

->withArgs(function ($sql, $bindings) {

$this->assertSame($sql, 'EXPLAIN select * from "users" where 0 = 1');

$this->assertIsArray($bindings);

$this->assertCount(0, $bindings);

return $sql === 'EXPLAIN select * from "users" where 0 = 1' && $bindings === [];

})

->andReturn([]);

$builder->select('*')->from('users')->whereIntegerInRaw('id', []);

$this->assertCount(0, $builder->explain());

$builder = $this->getBuilder();

/**

* @var ConnectionInterface|MockInterface $connection

*/

$connection = $builder->getConnection();

$connection->allows('select')

->once()

->withArgs(function ($sql, $bindings) {

$this->assertSame($sql, 'EXPLAIN select * from "hyperf" where "id" in (?, ?, ?)');

$this->assertIsArray($bindings);

$this->assertCount(3, $bindings);

return $sql === 'EXPLAIN select * from "hyperf" where "id" in (?, ?, ?)' && $bindings === [1, 2, 3];

})

->andReturn([]);

$builder->select('*')->from('hyperf')->whereIn('id', [1, 2, 3]);

$this->assertCount(0, $builder->explain());

}

public function testEmptyWhereIntegerNotInRaw(): void

{

$builder = $this->getBuilder();

$builder->select('*')->from('users')->whereIntegerNotInRaw('id', []);

$this->assertSame('select * from "users" where 1 = 1', $builder->toSql());

$this->assertEquals([], $builder->getBindings());

}

public function testBasicWhereColumn(): void

{

$builder = $this->getBuilder();

$builder->select('*')->from('users')->whereColumn('first_name', 'last_name')->orWhereColumn('first_name', 'middle_name');

$this->assertSame('select * from "users" where "first_name" = "last_name" or "first_name" = "middle_name"', $builder->toSql());

$this->assertEquals([], $builder->getBindings());

$builder = $this->getBuilder();

$builder->select('*')->from('users')->whereColumn('updated_at', '>', 'created_at');

$this->assertSame('select * from "users" where "updated_at" > "created_at"', $builder->toSql());

$this->assertEquals([], $builder->getBindings());

}

public function testArrayWhereColumn(): void

{

$conditions = [

['first_name', 'last_name'],

['updated_at', '>', 'created_at'],

];

$builder = $this->getBuilder();

$builder->select('*')->from('users')->whereColumn($conditions);

$this->assertSame('select * from "users" where ("first_name" = "last_name" and "updated_at" > "created_at")', $builder->toSql());

$this->assertEquals([], $builder->getBindings());

}

public function testJoinSubWithPrefix()

{

$builder = $this->getBuilder();

$builder->getGrammar()->setTablePrefix('prefix_');

$builder->from('users')->joinSub('select * from "contacts"', 'sub', 'users.id', '=', 'sub.id');

$this->assertEquals('select * from "prefix_users" inner join (select * from "contacts") as "prefix_sub" on "prefix_users"."id" = "prefix_sub"."id"', $builder->toSql());

}

public function testFromSubWithPrefix()

{

$builder = $this->getBuilder();

$builder->getGrammar()->setTablePrefix('prefix_');

$builder->fromSub(function ($query) {

$query->select(new Raw('max(last_seen_at) as last_seen_at'))->from('user_sessions')->where('foo', '=', '1');

}, 'sessions')->where('bar', '<', '10');

$this->assertEquals('select * from (select max(last_seen_at) as last_seen_at from "prefix_user_sessions" where "foo" = ?) as "prefix_sessions" where "bar" < ?', $builder->toSql());

$this->assertEquals(['1', '10'], $builder->getBindings());

}

public function testWhereFulltext()

{

$builder = $this->getMySqlBuilderWithProcessor();

$builder->select('*')->from('users')->whereFullText('body', 'Hello World');

$this->assertSame('select * from `users` where match (`body`) against (? in natural language mode)', $builder->toSql());

$this->assertEquals(['Hello World'], $builder->getBindings());

$builder = $this->getMySqlBuilderWithProcessor();

$builder->select('*')->from('users')->whereFullText('body', 'Hello World', ['expanded' => true]);

$this->assertSame('select * from `users` where match (`body`) against (? in natural language mode with query expansion)', $builder->toSql());

$this->assertEquals(['Hello World'], $builder->getBindings());

$builder = $this->getMySqlBuilderWithProcessor();

$builder->select('*')->from('users')->whereFullText('body', '+Hello -World', ['mode' => 'boolean']);

$this->assertSame('select * from `users` where match (`body`) against (? in boolean mode)', $builder->toSql());

$this->assertEquals(['+Hello -World'], $builder->getBindings());

$builder = $this->getMySqlBuilderWithProcessor();

$builder->select('*')->from('users')->whereFullText('body', '+Hello -World', ['mode' => 'boolean', 'expanded' => true]);

$this->assertSame('select * from `users` where match (`body`) against (? in boolean mode)', $builder->toSql());

$this->assertEquals(['+Hello -World'], $builder->getBindings());

$builder = $this->getMySqlBuilderWithProcessor();

$builder->select('*')->from('users')->whereFullText(['body', 'title'], 'Car,Plane');

$this->assertSame('select * from `users` where match (`body`, `title`) against (? in natural language mode)', $builder->toSql());

$this->assertEquals(['Car,Plane'], $builder->getBindings());

}

public function testWhereAll()

{

$builder = $this->getBuilder();

$builder->select('*')->from('users')->whereAll(['last_name', 'email'], '%Otwell%');

$this->assertSame('select * from "users" where ("last_name" = ? and "email" = ?)', $builder->toSql());

$this->assertEquals(['%Otwell%', '%Otwell%'], $builder->getBindings());

$builder = $this->getBuilder();

$builder->select('*')->from('users')->whereAll(['last_name', 'email'], 'not like', '%Otwell%');

$this->assertSame('select * from "users" where ("last_name" not like ? and "email" not like ?)', $builder->toSql());

$this->assertEquals(['%Otwell%', '%Otwell%'], $builder->getBindings());

}

public function testOrWhereAll()

{

$builder = $this->getBuilder();

$builder->select('*')->from('users')->where('first_name', 'like', '%Taylor%')->orWhereAll(['last_name', 'email'], 'like', '%Otwell%');

$this->assertSame('select * from "users" where "first_name" like ? or ("last_name" like ? and "email" like ?)', $builder->toSql());

$this->assertEquals(['%Taylor%', '%Otwell%', '%Otwell%'], $builder->getBindings());

$builder = $this->getBuilder();

$builder->select('*')->from('users')->where('first_name', 'like', '%Taylor%')->whereAll(['last_name', 'email'], 'like', '%Otwell%', 'or');

$this->assertSame('select * from "users" where "first_name" like ? or ("last_name" like ? and "email" like ?)', $builder->toSql());

$this->assertEquals(['%Taylor%', '%Otwell%', '%Otwell%'], $builder->getBindings());

$builder = $this->getBuilder();

$builder->select('*')->from('users')->where('first_name', 'like', '%Taylor%')->orWhereAll(['last_name', 'email'], '%Otwell%');

$this->assertSame('select * from "users" where "first_name" like ? or ("last_name" = ? and "email" = ?)', $builder->toSql());

$this->assertEquals(['%Taylor%', '%Otwell%', '%Otwell%'], $builder->getBindings());

}

public function testWhereAny()

{

$builder = $this->getBuilder();

$builder->select('*')->from('users')->whereAny(['last_name', 'email'], 'like', '%Otwell%');

$this->assertSame('select * from "users" where ("last_name" like ? or "email" like ?)', $builder->toSql());

$this->assertEquals(['%Otwell%', '%Otwell%'], $builder->getBindings());

$builder = $this->getBuilder();

$builder->select('*')->from('users')->whereAny(['last_name', 'email'], '%Otwell%');

$this->assertSame('select * from "users" where ("last_name" = ? or "email" = ?)', $builder->toSql());

$this->assertEquals(['%Otwell%', '%Otwell%'], $builder->getBindings());

}

public function testWhereNone()

{

$builder = $this->getBuilder();

$builder->select('*')->from('users')->whereNone(['last_name', 'email'], 'like', '%Otwell%');

$this->assertSame('select * from "users" where not ("last_name" like ? or "email" like ?)', $builder->toSql());

$this->assertEquals(['%Otwell%', '%Otwell%'], $builder->getBindings());

$builder = $this->getBuilder();

$builder->select('*')->from('users')->whereNone(['last_name', 'email'], 'Otwell');

$this->assertSame('select * from "users" where not ("last_name" = ? or "email" = ?)', $builder->toSql());

$this->assertEquals(['Otwell', 'Otwell'], $builder->getBindings());