mf / query-builder-composer
QueryBuilderComposer for easier composing Doctrine\ORM\QueryBuilder parts
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-10-29 01:10:01 UTC
README
QueryBuilderComposer for easier composing Doctrine\\ORM\\QueryBuilder
parts
Install
composer require mf/query-builder-composer
Compose parts for QueryBuilder
Parts
are array of:
- modifiers
- rules
Modifier:
Modifier
is ANY callable
by this pattern: (QueryBuilder -> QueryBuilder)
example of Modifiers
:
- (anonymus function): [ function(QueryBuilder $qb) { return $qb->select('...'); }, ... ]
- (static function) : [ [$this, 'modifyQueryBuilder'], ... ]
- (closure) : [ $addSelectModifier, ... ]
- (Modifier) : [ new Modifier('...'), ... ]
- ...
Rule:
Rule
represents any QueryBuilder
method call
- array of
strings
- array of single
string
(separator isspace
) - just a single
string
(separator isspace
)
Let's say we have this QueryBuilder
method call:
// method $queryBuilder->from('student', 's'); // Rule ['from', 'student', 's'] OR ['from student s'] OR 'from student s'
example of Rules
:
(QueryBuilder method call) : (rule representation)
- $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'
- ...
Usage
Why? What is a problem?
If you have complex methods for building Query
via QueryBuilder
, you might be in same situation as I am.
I have many similar methods to build different Queries
and I cant see a clear way how to reuse my QueryBuilder
parts.
So I decided to create this QueryBuilderComposer
to make this issue easier.
Example of complex methods with duplicated parts
Methods are simplified so they might not be 100% correct.
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(); }
Now you can have some idea of those parts which are same for more cases and they can be composed and defined once!
Composition of parts
Step 1 (rewrite to 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(); }
Step 2 (store common rules to class constants to allow easier reuse)
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(); }
Step 3 (compose parts)
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(); }
Step 4 (use syntax sugar over 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(); }
Difference between compose
vs 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);
Conclusion
You can merge, compose and reuse your QueryBuilder
parts easy.
Example above is just quick solution. You can do much more patterns over this composition
:
- implement
Modifier
to do something withQueryBuilder
- implement
Closure
to be reapplied again - ...
How to add complex rulex to 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(); }