yiisoft/data-db

Database query adapter for yiisoft/data data providers

dev-master / 3.0.x-dev 2024-11-19 15:07 UTC

Requires

• php: ^8.1
• yiisoft/data: dev-master
• yiisoft/db: ^1.3

Requires (Dev)

• maglnet/composer-require-checker: ^4.2
• phpunit/phpunit: ^10.5
• rector/rector: ^1.0
• roave/infection-static-analysis-plugin: ^1.16
• spatie/phpunit-watcher: ^1.23
• vimeo/psalm: ^5.21
• vlucas/phpdotenv: ^5.6
• yiisoft/cache: ^3.0
• yiisoft/db-mssql: ^1.2
• yiisoft/db-mysql: ^1.2
• yiisoft/db-oracle: ^1.3
• yiisoft/db-pgsql: ^1.3
• yiisoft/db-sqlite: ^1.2

BSD-3-Clause

• database
• db
• data provider

README

Yii Data DB

The package provides Yiisoft\Db\Query\Query bindings for generic data abstractions.

Detailed build statuses:

Requirements

• PHP 8.1 or higher.

Installation

The package could be installed with Composer:

composer require yiisoft/data-db

General usage

use Yiisoft\Data\Db\Filter\All;
use Yiisoft\Data\Db\Filter\Equals;
use Yiisoft\Data\Db\QueryDataReader;

$typeId    = filter_input(INPUT_GET, 'type_id', FILTER_VALIDATE_INT);
$countryId = filter_input(INPUT_GET, 'country_id', FILTER_VALIDATE_INT);
$parentId  = filter_input(INPUT_GET, 'parent_id', FILTER_VALIDATE_INT);

// OR
// $typeId    = $_GET['type_id'] ?? null;
// $countryId = $_GET['country_id'] ?? null;
// $parentId  = $_GET['parent_id'] ?? null;

// OR
// $params = $request->getQueryParams();
// $typeId    = $params['type_id'] ?? null;
// $countryId = $params['country_id'] ?? null;
// $parentId  = $params['parent_id'] ?? null;

// OR same with ArrayHelper::getValue();


$query = $arFactory->createQueryTo(AR::class);

$filter = new All(
    (new Equals('type_id', $typeId)),
    (new Equals('country_id', $countryId)),
    (new Equals('parent_id', $parentId))
);

$dataReader = (new QueryDataReader($query))
            ->withFilter($filter);

If $typeId, $countryId and $parentId equals NULL that generate SQL like:

SELECT AR::tableName().* FROM AR::tableName() WHERE type_id IS NULL AND country_id IS NULL AND parent_id IS NULL

If we want ignore not existing arguments (i.e. not set in $_GET/queryParams), we can use withIgnoreNull(true) method:

$typeId    = 1;
$countryId = null;
$parentId  = null;

$filter = new All(
    (new Equals('type_id', $typeId))->withIgnoreNull(true),
    (new Equals('country_id', $countryId))->withIgnoreNull(true),
    (new Equals('parent_id', $parentId))->withIgnoreNull(true)
);

$dataReader = (new QueryDataReader($query))
            ->withFilter($filter);

That generate SQL like:

SELECT AR::tableName().* FROM AR::tableName() WHERE type_id = 1

If query joins several tables with same column name, pass table name as 3-th filter arguments

$equalsTableOne = (new Equals('id', 1, 'table_one'))->withIgnoreNull(true);
$equalsTableTwo = (new Equals('id', 100, 'table_two'))->withIgnoreNull(true);

Current filters/processors

Compare

• Equals - =
• NotEquals - !=
• GreaterThan - >
• GreaterThanOrEqual - >=
• In
• LessThan - <
• LessThanOrEqual - <=
• Not
• Like\ILIke
• Exists
• Between

Filter "Like" or "ILike"

This filters has methods withBoth, withoutBoth, withStart, withoutStart, withEnd, withoutEnd

$filter = new Like('column', 'value');
$dataReader = (new QueryDataReader($query))->withFilter($filter);
//column LIKE '%value%'

$filter = (new Like('column', 'value'))->withoutStart();
$dataReader = (new QueryDataReader($query))->withFilter($filter);
//column LIKE 'value%'

$filter = (new Like('column', 'value'))->withoutEnd();
$dataReader = (new QueryDataReader($query))->withFilter($filter);
//column LIKE '%value'

Filter "Exists"

Takes only one argument with type ofYiisoft\Db\Query\Query

Filter "Not"

Takes only one argument with type ofYiisoft\Data\Reader\Filter\FilterInterface

Group

• All - and
• Any - or

Documentation

• Internals

If you need help or have a question, the Yii Forum is a good place for that. You may also check out other Yii Community Resources.

License

The Yii Data DB is free software. It is released under the terms of the BSD License. Please see LICENSE for more information.

Maintained by Yii Software.

Support the project

Follow updates