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