yiisoft/data-db

Database query adapater for yiisoft/data data providers

dev-master / 3.0.x-dev 2024-02-21 09:25 UTC

This package is auto-updated.

Last update: 2024-02-21 09:27:45 UTC


README

68747470733a2f2f796969736f66742e6769746875622e696f2f646f63732f696d616765732f7969695f6c6f676f2e737667

Yii Data DB


Latest Stable Version Total Downloads Build status Code Coverage Mutation testing badge static analysis type-coverage

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

Testing

Unit testing

The package is tested with PHPUnit. To run tests:

./vendor/bin/phpunit

Mutation testing

The package tests are checked with Infection mutation framework with Infection Static Analysis Plugin. To run it:

./vendor/bin/roave-infection-static-analysis-plugin

Static analysis

The code is statically analyzed with Psalm. To run static analysis:

./vendor/bin/psalm

License

The Yii _____ 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

Open Collective

Follow updates

Official website Twitter Telegram Facebook Slack