yiisoft/data-db

Database adapter for data interfaces

Yii Data DB

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

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

data-db's Issues

Make `AbstractQueryDataReader::getHandlerByOperation()` protected or add to interface

See #37

For this case:

$arQuery->with([
       'relation' => function($query) use ($dataReader, $filter) { 
               $handler = $dataReader->getHandlerByOperation($filter);
               $handler->applyFilter($query, $filter);
       }
])

better pass handler additionally.

Add test for all drivers.

Test filter handlers with query data reader and actual data, not only SQL

Increase coverage

No need format `DateTime` instances after support them by `yiisoft/db`

Related with yiisoft/db#725

data-db/src/Filter/CompareFilter.php

Lines 54 to 63 in 2cc323c

	protected function formatValue(mixed $value): mixed
	{
	$format = $this->dateTimeFormat ?? static::$mainDateTimeFormat;
	if ($format && $value instanceof DateTimeInterface) {
	return $value->format($format);
	}
	return $value;
	}

Add DataWriter

Fix mutants

`QueryDataReader::createItem()` shold be realized in `Db\Query` class

QueryDataReader::createItem() method should be removed

data-db/src/QueryDataReader.php

Lines 17 to 21 in 174652b

	protected function createItem(array|object $row): array|object
	{
	/** @psalm-var TValue */
	return $row;
	}

BatchQueryResult already has property Closure $populateMethod to prepare row result

https://github.com/yiisoft/db/blob/e380ae2ffb6f3a466e6f168fafa93548e50f0d8c/src/Query/BatchQueryResult.php#L109

Instead of these, Db\Query class can contain property Closure $callback (and withCallback() method) which will be called for each row from result.

The $callback will be specified in one place (Query) instead of many different realizations. And this will allow to use Query directly to get prepared result (without any data reader).