yiisoft / data-db
Database query adapter for yiisoft/data data providers
Fund package maintenance!
Opencollective
yiisoft
Installs: 1 265
Dependents: 0
Suggesters: 0
Security: 0
Stars: 12
Watchers: 18
Forks: 4
Open Issues: 8
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
This package is auto-updated.
Last update: 2024-09-26 14:04:18 UTC
README
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
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.