mf / query-builder-composer
QueryBuilderComposer,简化 Doctrine\ORM\QueryBuilder 部分的组合
1.0.0
2017-07-30 14:36 UTC
Requires
- doctrine/orm: ^2.1
- lstrojny/functional-php: ^1.6
Requires (Dev)
- friendsofphp/php-cs-fixer: ^2.4
- mockery/mockery: ^0.9.9
- phpstan/phpstan: ^0.8.0
- phpunit/phpunit: ^6.2
- satooshi/php-coveralls: ^1.0
- squizlabs/php_codesniffer: ^3.0
This package is auto-updated.
Last update: 2024-08-29 00:49:00 UTC
README
QueryBuilderComposer,简化 Doctrine\\ORM\\QueryBuilder
部分的组合
安装
composer require mf/query-builder-composer
组合 QueryBuilder
部分以简化查询构建
部分
是一个包含以下内容的数组
- 修饰符
- 规则
修饰符
修饰符
是任何符合此模式的 callable
:(QueryBuilder -> QueryBuilder)
修饰符
的示例
- (anonymus function): [ function(QueryBuilder $qb) { return $qb->select('...'); }, ... ]
- (static function) : [ [$this, 'modifyQueryBuilder'], ... ]
- (closure) : [ $addSelectModifier, ... ]
- (Modifier) : [ new Modifier('...'), ... ]
- ...
规则
规则
表示任何 QueryBuilder
方法调用
- 字符串数组
- 单个字符串数组(分隔符为
空格
) - 单个字符串(分隔符为
空格
)
假设我们有这个 QueryBuilder
方法调用
// method $queryBuilder->from('student', 's'); // Rule ['from', 'student', 's'] OR ['from student s'] OR 'from student s'
规则
的示例
(QueryBuilder 方法调用):(规则表示)
- $qb->select('t.column') : ['select', 't.column']
- $qb->join('t.joined', 'j') : ['join', 't.joined', 'j']
- $qb->from('table', 't') : ['from', 'table', 't']
- $qb->from('table', 't') : ['from table t']
- $qb->from('table', 't') : 'from table t'
- ...
用法
为什么?有什么问题?
如果你有通过 QueryBuilder
构建 Query
的复杂方法,你可能和我一样。我有很多类似的方法来构建不同的 Queries
,而且我找不到一个清晰的方法来重用我的 QueryBuilder
部分。
因此,我决定创建这个 QueryBuilderComposer
来简化这个问题。
具有重复部分的复杂方法的示例
这些方法被简化了,可能不是 100% 正确。
public function countFreeApproved() { return $this->createQueryBuilder('c') ->select('COUNT(c.id)') ->where('c.price = 0') ->andWhere('c.approved = TRUE') ->getQuery() ->getSingleScalarResult(); } public function findMostViewedFreeCourses() { return $this->createQueryBuilder('c') ->select('c, i, COUNT(views) AS HIDDEN views') ->innerJoin('c.image', 'i') ->where('c.approved = TRUE') ->andWhere('c.price = 0') ->orderBy('views', 'DESC') ->addOrderBy('c.position', 'ASC') ->getQuery() ->getResult(); } public function findFreeCourses() { return $this->createQueryBuilder('c') ->select('c, i') ->innerJoin('c.image', 'i') ->where('c.approved = TRUE') ->andWhere('c.price = 0') ->addOrderBy('c.position', 'ASC') ->getQuery() ->getResult(); }
现在你可以了解那些在更多情况下相同的部分,它们可以组合并定义一次!
部分组合
步骤 1(重写为 QueryBuilderComposer
)
public function countFreeApproved() { return $queryBuilderComposer ->compose( $this->createQueryBuilder('c'), [ ['select', 'COUNT(c.id)'], ['where', 'c.price = 0'], ['andWhere', 'c.approved = TRUE'], ] ) ->getQuery() ->getResult(); } public function findMostViewedFreeCourses() { return $queryBuilderComposer ->compose( $this->createQueryBuilder('c'), [ ['select', 'c, i, COUNT(views) AS HIDDEN views'], ['innerJoin', 'c.image', 'i'], ['where', 'c.approved = TRUE'], ['andWhere', 'c.price = 0'], ['orderBy', 'views', 'DESC'], ['addOrderBy', 'c.position', 'ASC'], ] ) ->getQuery() ->getResult(); } public function findFreeCourses() { return $queryBuilderComposer ->compose( $this->createQueryBuilder('c'), [ ['select', 'c, i'], ['innerJoin', 'c.image', 'i'], ['where', 'c.approved = TRUE'], ['andWhere', 'c.price = 0'], ['addOrderBy', 'c.position', 'ASC'], ] ) ->getQuery() ->getResult(); }
步骤 2(将常用规则存储为类常量,以便更容易重用)
const SELECT_COURSE = ['select', 'c, i']; const JOIN_IMAGE = ['innerJoin', 'c.image', 'i']; const FREE_COURSES = ['andWhere', 'c.price = 0']; const APPROVED_ONLY = ['andWhere', 'c.approved = TRUE']; const DEFAULT_ORDER = ['addOrderBy', 'c.position', 'ASC']; public function countFreeApproved() { return $this->queryBuilderComposer ->compose( $this->createQueryBuilder('c'), [ ['select', 'COUNT(c.id)'], self::FREE_COURSES, self::APPROVED_ONLY, ] ) ->getQuery() ->getResult(); } public function findMostViewedFreeCourses() { return $this->queryBuilderComposer ->compose( $this->createQueryBuilder('c'), [ self::SELECT_COURSE, ['COUNT(views) AS HIDDEN views'], self::JOIN_IMAGE, self::FREE_COURSES, self::APPROVED_ONLY, ['orderBy', 'views', 'DESC'], self::DEFAULT_ORDER, ] ) ->getQuery() ->getResult(); } public function findFreeCourses() { return $this->queryBuilderComposer ->compose( $this->createQueryBuilder('c'), [ self::SELECT_COURSE, self::JOIN_IMAGE, self::FREE_COURSES, self::APPROVED_ONLY, self::DEFAULT_ORDER, ] ) ->getQuery() ->getResult(); }
步骤 3(组合部分)
const SELECT_COURSE = ['select', 'c, i']; const JOIN_IMAGE = ['innerJoin', 'c.image', 'i']; const FREE_COURSES = ['andWhere', 'c.price = 0']; const APPROVED_ONLY = ['andWhere', 'c.approved = TRUE']; const DEFAULT_ORDER = ['addOrderBy', 'c.position', 'ASC']; const SELECT_COURSE_W_IMAGE = [ self::SELECT_COURSE, self::JOIN_IMAGE, ]; const FREE_APPROVED = [ self::FREE_COURSES, self::APPROVED_ONLY, ]; public function countFreeApproved() { return $this->queryBuilderComposer ->compose( $this->createQueryBuilder('c'), array_merge( [['select', 'COUNT(c.id)']], self::FREE_APPROVED ) ) ->getQuery() ->getResult(); } public function findMostViewedFreeCourses() { return $this->queryBuilderComposer ->compose( $this->createQueryBuilder('c'), array_merge( self::SELECT_COURSE_W_IMAGE, [ ['COUNT(views) AS HIDDEN views'], ['orderBy', 'views', 'DESC'], self::DEFAULT_ORDER, ], self::FREE_APPROVED ) ) ->getQuery() ->getResult(); } public function findFreeCourses() { return $this->queryBuilderComposer ->compose( $this->createQueryBuilder('c'), array_merge( self::SELECT_COURSE_W_IMAGE, [self::DEFAULT_ORDER], self::FREE_APPROVED ) ) ->getQuery() ->getResult(); }
步骤 4(使用 语法糖 而不是 array_merge
)
public function countFreeApproved() { return $this->queryBuilderComposer ->mergeCompose( $this->createQueryBuilder('c'), [['select', 'COUNT(c.id)']], self::FREE_APPROVED ) ->getQuery() ->getResult(); } public function findMostViewedFreeCourses() { return $this->queryBuilderComposer ->mergeCompose( $this->createQueryBuilder('c'), self::SELECT_COURSE_W_IMAGE, [ ['COUNT(views) AS HIDDEN views'], ['orderBy', 'views', 'DESC'], self::DEFAULT_ORDER, ], self::FREE_APPROVED ) ->getQuery() ->getResult(); } public function findFreeCourses() { return $this->queryBuilderComposer ->mergeCompose( $this->createQueryBuilder('c'), self::SELECT_COURSE_W_IMAGE, [self::DEFAULT_ORDER], self::FREE_APPROVED ) ->getQuery() ->getResult(); }
compose
与 mergeCompose
之间的区别
$baseParts = [ 'select s.id s.name s.age', 'from student s', ]; $approvedMature = [ ['andWhere', 's.approved = true'], ['andWhere', 's.age >= 18'], ]; // following calls are the same! $queryBuilder = $composer->compose($this->queryBuilder, array_merge($baseParts, $approvedMature)); $queryBuilder = $composer->mergeCompose($this->queryBuilder, $baseParts, $approvedMature);
结论
你可以轻松地合并、组合和重用你的 QueryBuilder
部分示例只是一个快速解决方案。你可以在此基础上实现更多模式。
- 实现
Modifier
以对QueryBuilder
执行某些操作 - 实现
Closure
以允许再次应用 - ...
如何将复杂规则添加到 QueryBuilder
public function complexResult() { $queryBuilder = $this->createQueryBuilder('c'); $queryBuilder->... // do anything you want with QueryBuilder here return $this->queryBuilderComposer ->compose( $queryBuilder, [ // add more parts here... , function(QueryBuilder $queryBuilder) { return $queryBuilder->... // do anything you want with QueryBuilder here either }, // add more parts here... , ] ) ->getQuery() ->getResult(); }