Query Builder Composer

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 is space)
• just a single string (separator is space)

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 with QueryBuilder
• 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();
}