Download the PHP package sirprize/queried without Composer
On this page you can find all versions of the php package sirprize/queried. It is possible to download/install these versions without Composer. Possible dependencies are resolved automatically.
Download sirprize/queried
More information about sirprize/queried
Files in sirprize/queried
Package queried
Short Description Database/ORM-agnostic query construction helper
License MIT
Homepage https://github.com/sirprize/queried
Informations about the package queried
Queried
Database/ORM-agnostic query construction helper
Description
Parsing input, setting defaults and constructing SELECT
queries can quickly become a mess. Queried helps organizing clauses for WHERE
, HAVING
and ORDER BY
, making it easy to apply input as well as defaults when executing the query.
Usage
Creating and activating simple WHERE conditions
First we'll take a look at organizing conditions for the WHERE
and HAVING
parts of a query statement. The basic idea is to prepare clauses describing specific conditions, register them with a query object and then activate them individually based on application requirements and user input. Each clause is wrapped in a BaseCondition
object. Those objects are then registered with a BaseQueryConfigurator
object:
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)
;
Next we'll activate some of the conditions according application requirements. Only published releases with a release-date of today or older must make it into the result:
$queryConfigurator->getConditionRegistry()->activateCondition('published');
Then the user can choose from digital or physical releases by setting the format
parameter (eg /releases?format=digital
)
$format = (array_key_exists('format', $_GET)) ? $_GET['format'] : null;
if ($format === 'digital')
{
$queryConfigurator->getConditionRegistry()->activateCondition('digital');
}
else {
$queryConfigurator->getConditionRegistry()->activateCondition('physical');
}
And finally we'll collect the activated conditions and add them to our query statement:
foreach ($queryConfigurator->getActiveConditions() as $condition)
{
$clause = $condition->getClause();
// Add clause to query
}
More complex WHERE conditions
For more complex conditions we'll subclass BaseCondition
. This allows us to share conditions between queries. It's also the right place to set sensible defaults in case of invalid input:
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;
}
}
Note the $tokenizer
parameter in the build()
method - this is a simple object with an internal counter, letting us define non-conflicting parameter names. This is useful when combining many BaseCondition
objects into one query.
Next we'll instantiate and build the condition, providing values from user input:
$artist = (array_key_exists('artist', $_GET)) ? $_GET['artist'] : null;
$artistCondition = new ArtistCondition('release');
$artistCondition
->addValue('artist', $artist)
->build(new Tokenizer())
;
And when it's time to assemble the query, we have the final clause plus parameters available:
$condition = $artistCondition->getClause(); // "release.artist LIKE :token0"
$params = $artistCondition->getParams(); // array('token0' => '%Rebolledo%')
Defining sorting
Sorting is normally expressed by one or more field names, each with a direction of either ascending or descending (eg ORDER BY release.date DESC, release.title ASC
). This information is stored in a Rule
object, both for ascending and descending order:
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')
The Sorting class maps rule names (eg from user input) to rules while applying defaults, given a non-existing rule name. It makes sure that only valid column definitions make it into the query. Let's put it all together:
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')
;
No defaults, no parameters
$params = new Params();
$sorting = new Sorting();
$sorting->setRules($rules);
$sorting->setParams($params);
$columns = $sorting->getColumns(); // array();
Single default
$defaults = new Params('title', 'asc');
$sorting = new Sorting();
$sorting->setRules($rules);
$sorting->setDefaults($defaults);
$columns = $sorting->getColumns(); // array('release.title' => 'asc');
Defaults and valid parameters
$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');
No defaults and invalid parameters (non-existing rule name)
$params = new Params('xxx', 'asc');
$sorting = new Sorting();
$sorting->setRules($rules);
$sorting->setParams($params);
$columns = $sorting->getColumns(); // array();
No defaults and invalid parameters (invalid ordering, valid orderings are "asc" or "desc")
$params = new Params('date', 'xxx');
$sorting = new Sorting();
$sorting->setRules($rules);
$sorting->setParams($params);
$columns = $sorting->getColumns(); // array('release.date' => 'desc');
Putting it all together
It's best to manage the construction of the entire query in a subclass of BaseQueryConfigurator
. Here's an example of a query built for use with the 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));
}
}
}
}
Running the query
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
See LICENSE.