README

数据库/ORM无关的查询构建助手

描述

解析输入、设置默认值和构建SELECT查询很快就会变得混乱。Queried帮助组织WHERE、HAVING和ORDER BY子句，使得在执行查询时应用输入以及默认值变得容易。

用法

创建和激活简单的WHERE条件

首先，我们将查看查询语句的WHERE和HAVING部分的条件组织。基本思想是准备描述特定条件的子句，将其注册到查询对象中，然后根据应用需求和用户输入逐个激活它们。每个子句都封装在一个BaseCondition对象中。然后，将这些对象注册到BaseQueryConfigurator对象中。

use Sirprize\Queried\BaseQueryConfigurator;
use Sirprize\Queried\Condition\BaseCondition;

$publishedCondition = new BaseCondition();
$publishedCondition->setClause("(release.date <= CURRENT_DATE() AND release.published = 1)");

$physicalCondition = new BaseCondition();
$physicalCondition->setClause("(release.format = 'LP' OR release.format = 'CD'");

$digitalCondition = new BaseCondition();
$digitalCondition->setClause("(release.format = 'MP3' OR release.format = 'WAV'");

$queryConfigurator = new BaseQueryConfigurator();

$queryConfigurator->getConditionRegistry()
    ->registerCondition('published', $publishedCondition)
    ->registerCondition('physical', $physicalCondition)
    ->registerCondition('digital', $physicalCondition)
;

接下来，我们将根据应用需求激活一些条件。只有今天或更早发布的已发布版本必须包含在结果中。

$queryConfigurator->getConditionRegistry()->activateCondition('published');

然后，用户可以通过设置format参数（例如/releases?format=digital）从数字或物理版本中选择。

$format = (array_key_exists('format', $_GET)) ? $_GET['format'] : null;

if ($format === 'digital')
{
    $queryConfigurator->getConditionRegistry()->activateCondition('digital');
}
else {
    $queryConfigurator->getConditionRegistry()->activateCondition('physical');
}

最后，我们将收集激活的条件并将它们添加到我们的查询语句中。

foreach ($queryConfigurator->getActiveConditions() as $condition)
{
    $clause = $condition->getClause();
    // Add clause to query
}

更复杂的WHERE条件

对于更复杂的条件，我们将子类化BaseCondition。这允许我们在查询之间共享条件。这也是在无效输入的情况下设置合理默认值的好地方。

use Sirprize\Queried\Condition\BaseCondition;
use Sirprize\Queried\Condition\Tokenizer;

class ArtistCondition extends BaseCondition
{
    protected $alias = null;

    public function __construct($alias = '')
    {
        $this->alias = $alias;
    }

    public function build(Tokenizer $tokenizer = null)
    {
        $this->reset();

        $artist = $this->getValue('artist');

        if (!$artist)
        {
            return $this;
        }

        $token = $tokenizer->make();
        $alias = ($this->alias) ? $this->alias . '.' : $this->alias;

        $this
            ->setClause("{$alias}artist LIKE :$token")
            ->addParam($token, '%'.$artist.'%')
        ;

        return $this;
    }
}

注意build()方法中的$tokenizer参数 - 这是一个具有内部计数的简单对象，让我们能够定义不冲突的参数名称。当将许多BaseCondition对象组合成一个查询时，这很有用。

接下来，我们将实例化和构建条件，提供来自用户输入的值。

$artist = (array_key_exists('artist', $_GET)) ? $_GET['artist'] : null;
$artistCondition = new ArtistCondition('release');

$artistCondition
    ->addValue('artist', $artist)
    ->build(new Tokenizer())
;

当组装查询时，我们就有最终的子句以及可用参数。

$condition = $artistCondition->getClause(); // "release.artist LIKE :token0"
$params = $artistCondition->getParams(); // array('token0' => '%Rebolledo%')

定义排序

排序通常通过一个或多个字段名表示，每个字段名都有一个升序或降序的方向（例如ORDER BY release.date DESC, release.title ASC）。这些信息存储在Rule对象中，包括升序和降序。

use Sirprize\Queried\Sorting\Rule;

$dateRule = new Rule();

$dateRule
    ->addAscColumn('release.date', 'desc')
    ->addAscColumn('release.date', 'asc')
    ->addDescColumn('release.date', 'asc')
    ->addDescColumn('release.date', 'asc')
    ->setDefaultDirection('asc')
;

$columns = $dateRule->getAscColumns(); // array('release.date' => 'desc', 'release.title.asc')
$columns = $dateRule->getDescColumns(); // array('release.date' => 'asc', 'release.title.asc')

排序类将规则名称（例如来自用户输入）映射到规则，并在给定不存在的规则名称的情况下应用默认值。它确保只有有效的列定义包含在查询中。让我们将所有这些放在一起。

use Sirprize\Queried\Sorting\Params;
use Sirprize\Queried\Sorting\Rules;
use Sirprize\Queried\Sorting\Sorting;

$rules = new Rules();

$rules->newRule('title')
    ->addAscColumn('release.title', 'asc')
    ->addDescColumn('release.title', 'desc')
    ->setDefaultDirection('asc')
;

$rules->newRule('date')
    ->addAscColumn('release.date', 'asc')
    ->addDescColumn('release.date', 'desc')
    ->setDefaultDirection('desc')
;

没有默认值，没有参数

$params = new Params();
$sorting = new Sorting();
$sorting->setRules($rules);
$sorting->setParams($params);
$columns = $sorting->getColumns(); // array();

单个默认值

$defaults = new Params('title', 'asc');
$sorting = new Sorting();
$sorting->setRules($rules);
$sorting->setDefaults($defaults);
$columns = $sorting->getColumns(); // array('release.title' => 'asc');

默认值和有效参数

$params = new Params('date', 'asc');
$defaults = new Params('title', 'asc');
$sorting = new Sorting();
$sorting->setRules($rules);
$sorting->setParams($params);
$sorting->setDefaults($defaults);
$columns = $sorting->getColumns(); // array('release.date' => 'asc');

没有默认值和无效参数（不存在的规则名称）

$params = new Params('xxx', 'asc');
$sorting = new Sorting();
$sorting->setRules($rules);
$sorting->setParams($params);
$columns = $sorting->getColumns(); // array();

没有默认值和无效参数（无效排序，有效的排序是"asc"或"desc"）

$params = new Params('date', 'xxx');
$sorting = new Sorting();
$sorting->setRules($rules);
$sorting->setParams($params);
$columns = $sorting->getColumns(); // array('release.date' => 'desc');

将它们全部放在一起

最好在BaseQueryConfigurator的子类中管理整个查询的构建。以下是一个为与Doctrine ORM一起使用而构建的查询示例。

use Doctrine\ORM\EntityManager;
use Sirprize\Queried\BaseQueryConfigurator;

class ReleaseQueryConfigurator extends BaseQueryConfigurator
{
    protected $queryBuilder = null;
    protected $releaseAlias = 'release';

    public function __construct(EntityManager $entityManager)
    {
        $this->queryBuilder = $entityManager->createQueryBuilder();

        // register the external condition we built earlier
        $this->getConditionRegistry()->registerCondition('artist', new ArtistCondition($this->releaseAlias));

        // register an inline condition
        $pc = new BaseCondition();
        $pc->setClause("({$this->releaseAlias}.date <= CURRENT_DATE() AND {$this->releaseAlias}.published = 1)");
        $this->getConditionRegistry()->registerCondition('published', $pc);
        
        // define some sorting rules
        $this->getSorting()->getRules()->newRule('title')
            ->addAscColumn($this->releaseAlias.'.title', 'asc')
            ->addDescColumn($this->releaseAlias.'.title', 'desc')
            ->setDefaultDirection('asc')
        ;

        $this->getSorting()->getRules()->newRule('artist')
            ->addAscColumn($this->releaseAlias.'.artist', 'asc')
            ->addDescColumn($this->releaseAlias.'.artist', 'desc')
            ->setDefaultDirection('asc')
        ;
    }

    public function getCountQuery()
    {
        $this->reset();
        $this->applyFrom();
        $this->applyConditions();
        
        return $this->queryBuilder
            ->select("COUNT({$this->releaseAlias}.id)")
            ->getQuery()
        ;
    }
    
    public function getQuery()
    {
        $this->reset();
        $this->applyFrom();
        $this->applyConditions();
        $this->applySorting();

        return $this->queryBuilder
            ->select($this->releaseAlias)
            ->getQuery()
        ;
    }

    protected function applyFrom()
    {
        $this->queryBuilder
            ->from('My\Model\Entity\Product', $this->releaseAlias)
        ;
    }

    public function reset()
    {
        $this->queryBuilder
            ->resetDQLParts()
            ->setParameters(new ArrayCollection())
        ;
    }

    public function applySorting()
    {
        foreach ($this->getSorting()->getColumns() as $column => $order)
        {
            $this->queryBuilder->addOrderBy($column, $order);
        }
    }

    protected function applyConditions()
    {
        foreach ($this->getConditionRegistry()->getActiveConditions() as $condition)
        {
            $condition->build($this->getTokenizer());

            if (!$condition->getClause())
            {
                continue;
            }

            $this->queryBuilder->andWhere($condition->getClause());

            foreach ($condition->getParams() as $name => $value)
            {
                $this->queryBuilder->setParameter($name, $value, $condition->getType($name));
            }
        }
    }
}

运行查询

use Sirprize\Queried\Sorting\Params as SortingParams;

// input
$sort = (array_key_exists('sort', $_GET)) ? $_GET['sort'] : null;
$order = (array_key_exists('order', $_GET)) ? $_GET['order'] : null;
$label = (array_key_exists('label', $_GET)) ? $_GET['label'] : null;
$artist = (array_key_exists('artist', $_GET)) ? $_GET['artist'] : null;

// sorting
$sortingParams = new SortingParams($sort, $order);
$sortingDefaults = new SortingParams('title', 'asc');

// the query
$queryConfigurator = new ReleaseQueryConfigurator($em);

$queryConfigurator->getConditionRegistry()
    ->activateCondition('published')
    ->activateCondition('artist', array('artist' => $artist))
;

$queryConfigurator->getSorting()->setParams($sortingParams);
$queryConfigurator->getSorting()->setDefaults($sortingDefaults);

$count = $queryConfigurator->getCountQuery()->getSingleResult();
$releases = $queryConfigurator->getQuery()->getResult();

许可证

见LICENSE。

sirprize / queried

维护者

详细信息