包简介

PHP library to transform url query into db partial queries

README 文档

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

los/uql 适用场景与选型建议

los/uql 是一款基于 PHP 开发的 Composer 扩展包，目前已累计 37.45k 次下载、GitHub Stars 达 4，最近一次更新时间为 2018 年 04 月 19 日，在 PHP 生态内属于活跃度较高的组件。

我们在过去多个企业项目中使用过 los/uql 或与其功能相近的方案，如果你在选型或落地过程中遇到问题，例如 版本兼容、二次改造、私有化封装、与内部系统对接、生产 BUG 排查，欢迎联系我们协助评估。

围绕 los/uql 我们能提供哪些服务？

定制开发 / 二次开发

基于 los/uql 在你已有业务上做功能扩展、字段裁剪、UI 适配、与内部账号 / 权限 / 日志系统的深度对接。

BUG 修复 & 性能优化

线上偶发问题、内存泄漏、慢查询、并发异常等排查修复；针对高流量场景做缓存、队列、索引层面的调优。

项目外包 & 长期维护

承接完整的项目从需求 → 设计 → 开发 → 上线 → 长期运维；也可按月提供技术保姆服务。

yvsm@zunyunkeji.com QQ：316430983 微信：yvsm316 西安尊云信息科技 · 专注 PHP / Go / 分布式系统研发

los/uql

包简介