los/uql

PHP library to transform url query into db partial queries

1.2.0 2023-09-19 17:34 UTC

Requires

• php: ^8.1
• psr/http-message: ^1.0 || ^2.0

Requires (Dev)

• doctrine/coding-standard: ^12.0
• laminas/laminas-db: ^2.9
• laminas/laminas-diactoros: ^3.2
• php-coveralls/php-coveralls: ^2.6
• phpstan/phpstan: ^1.10
• phpunit/phpunit: ^10.3
• squizlabs/php_codesniffer: ^3.7

MIT

• Leandro Silva

README

This library utilizes url query parameters and generates db queries.

At this moment, it provides integration with:

• zend-db

Planned:

• mongodb

Installing

 composer require los/uql

Usage

The builder uses the query parameters 'q' for the queries and 'h' for hint (sort, order, limits, etc). You can change these in the constructor:

$builder = new ZendDbBuilder($select, 'query', 'hint');

The Select instance returned by the builder methods is a clone from the one passed in the constructor.

Zend DB

Passing the request directly:

public function handle(ServerRequestInterface $request): ResponseInterface
{
    $select = new \Laminas\Db\Select('table');
    $select = (new ZendDbBuilder($select))->fromRequest($request);
    $statement = $sql->prepareStatementForSqlObject($select);
    $results = $statement->execute();
}

or manually passing the parameters:

public function handle(ServerRequestInterface $request): ResponseInterface
{
    $queryParams = $request->getQueryParams();
    $query = $queryParams['q'] ?? [];
    $hint = $queryParams['h'] ?? [];

    $select = new \Laminas\Db\Select('table');
    $select = (new ZendDbBuilder($select))->fromParams($query, $hint);
    $statement = $sql->prepareStatementForSqlObject($select);
    $results = $statement->execute();
}

Examples:

operation	url query	select
equal	?q={"id":1}	WHERE id = 1
not	?q={"id":{"$not":1}}	WHERE id != 1
in	?q={"id":{"$in":[1,2]}}	WHERE id IN (1, 2)
nin	?q={"id":{"$nin":[1,2]}}	WHERE id NOT IN (1, 2)
like	?q={"name":{"$like":"John%"}}	WHERE name LIKE 'John%'
null	?q={"$null":"name"}	WHERE name IS NULL
not null	?q={"$nnull":"name"}	WHERE name IS NOT NULL
and	?q={"$and":[{"id":1},{"name":"John"}]}	WHERE id = 1 AND name = 'John'
or	?q={"$or":[{"id":1},{"name":"John"}]}	WHERE id = 1 OR name = 'John'
greater	?q={"price":{"$gt":100}}	WHERE price > 100
greater or equal	?q={"price":{"$gte":100}}	WHERE price >= 100
less	?q={"price":{"$lt":100}}	WHERE price < 100
less or equal	?q={"price":{"$lte":100}}	WHERE price <= 100
between	?q={"price":{"$bt":[100,200]}}	WHERE price >= 100 AND price <= 200

You can mix and nest queries:

url query	select
?q={"id":{"$not":1},"$or":[{"id":2},{"id":"3"}],"$and":[{"id":2},{"name":"test"}]}	WHERE "id" != '1' AND ("id" = '2' OR "id" = '3') AND ("id" = '2' AND "name" = 'test')
?q={"$or":[{"$and":[{"id":1},{"name":"test"}]},{"id":{"$not":1}},{"name":"test"}]}	WHERE (("id" = '1' AND "name" = 'test') OR "id" != '1' OR "name" = 'test')

Hint examples:

operation	url query	select
sort	?q={"id":1}&h={"$sort":"name"}	WHERE id = 1 ORDER BY name asc, price DESC
sort	?q={"id":1}&h={"$sort":{"name":"asc","price":-1}}	WHERE id = 1 ORDER BY name asc, price DESC
limit	?q={}&h={"$limit":10}	SELECT * FROM table LIMIT 10
limit + skip	?q={}&h={"$limit":10,"$skip":20}	SELECT * FROM table LIMIT 10 SKIP 10