334 lines
9.5 KiB
PHP
334 lines
9.5 KiB
PHP
<?php
|
|
|
|
namespace Query;
|
|
|
|
use Doctrine\DBAL\Connection;
|
|
use KupShop\KupShopBundle\Exception\ForceEmptyResultException;
|
|
|
|
class Operator
|
|
{
|
|
/**
|
|
* @return callable
|
|
*/
|
|
public static function andX($operand1, $operand2 = null)
|
|
{
|
|
if (func_num_args() === 1 && is_array($operand1)) {
|
|
$operands = $operand1;
|
|
} else {
|
|
$operands = func_get_args();
|
|
}
|
|
|
|
$operands = array_filter($operands);
|
|
|
|
if (!$operands) {
|
|
return null;
|
|
}
|
|
|
|
return function (QueryBuilder $qb) use ($operands) {
|
|
$evaluatedOperands = array_filter($qb->evaluateClosures($operands));
|
|
|
|
if (!$evaluatedOperands) {
|
|
return null;
|
|
}
|
|
|
|
return call_user_func_array([$qb->expr(), 'andX'], array_values($evaluatedOperands));
|
|
};
|
|
}
|
|
|
|
/**
|
|
* @return callable
|
|
*/
|
|
public static function orX($operand1, $operand2 = null)
|
|
{
|
|
if (func_num_args() === 1 && is_array($operand1)) {
|
|
$operands = $operand1;
|
|
} else {
|
|
$operands = func_get_args();
|
|
}
|
|
|
|
$operands = array_filter($operands);
|
|
|
|
if (!$operands) {
|
|
return '0';
|
|
}
|
|
|
|
return function (QueryBuilder $qb) use ($operands) {
|
|
return call_user_func_array([$qb->expr(), 'orX'], $qb->evaluateClosures($operands));
|
|
};
|
|
}
|
|
|
|
public static function between($field, \Range $range)
|
|
{
|
|
static $counter = 0;
|
|
|
|
return function (QueryBuilder $qb) use ($field, $range, &$counter) {
|
|
$e = $qb->expr();
|
|
$andXArgs = [];
|
|
|
|
if (($range->min() ?? '') !== '') {
|
|
$minParam = ':between_min_'.$counter;
|
|
$qb->setParameter($minParam, $range->min());
|
|
$andXArgs[] = $e->gte($field, $minParam);
|
|
}
|
|
|
|
if (($range->max() ?? '') !== '') {
|
|
$maxParam = ':between_max_'.$counter;
|
|
$qb->setParameter($maxParam, $range->max());
|
|
$andXArgs[] = $e->lte($field, $maxParam);
|
|
}
|
|
|
|
$counter++;
|
|
|
|
return static::andX($andXArgs);
|
|
};
|
|
}
|
|
|
|
/**
|
|
* @param string $set
|
|
* @param string $operator AND/OR
|
|
*
|
|
* @return callable
|
|
*/
|
|
public static function findInSet(array $needles, $set, $operator = 'AND')
|
|
{
|
|
if (!$needles) {
|
|
throw new \InvalidArgumentException('Array $needles must not be empty');
|
|
}
|
|
|
|
if (!$set) {
|
|
throw new \InvalidArgumentException('Parameter $set must not be empty');
|
|
}
|
|
|
|
if (!in_array($operator, ['AND', 'OR'])) {
|
|
throw new \InvalidArgumentException('Parameter $operator must be AND or OR');
|
|
}
|
|
|
|
static $counter = 0;
|
|
|
|
return function (QueryBuilder $qb) use ($needles, $set, $operator, &$counter) {
|
|
$expressions = [];
|
|
foreach ($needles as $needle) {
|
|
$paramName = ':needle_'.$counter++;
|
|
$quotedSet = $qb->getConnection()->quoteIdentifier($set);
|
|
$expressions[] = "FIND_IN_SET({$paramName}, {$quotedSet})";
|
|
$qb->setParameter($paramName, $needle);
|
|
}
|
|
|
|
return call_user_func_array([$qb->expr(), $operator.'X'], $expressions);
|
|
};
|
|
}
|
|
|
|
/**
|
|
* @param array $ids array of integers to search for in $field
|
|
* @param string $field field name to search
|
|
*
|
|
* @return callable
|
|
*/
|
|
public static function inIntArray(array $ids, $field)
|
|
{
|
|
static $counter = 0;
|
|
|
|
return function (QueryBuilder $qb) use ($ids, $field, &$counter) {
|
|
if (!$ids) {
|
|
return 'FALSE';
|
|
}
|
|
$paramName = 'inIntArray_'.$counter++;
|
|
$qb->setParameter($paramName, $ids, Connection::PARAM_INT_ARRAY);
|
|
|
|
return "{$field} IN (:{$paramName})";
|
|
};
|
|
}
|
|
|
|
public static function inSubQuery(string $field, QueryBuilder $subQuery): \Closure
|
|
{
|
|
return function (QueryBuilder $qb) use ($subQuery, $field) {
|
|
$qb->addParameters($subQuery->getParameters(), $subQuery->getParameterTypes());
|
|
|
|
return " {$field} IN ({$subQuery->getSQL()})";
|
|
};
|
|
}
|
|
|
|
/**
|
|
* @param array $ids array of integers to search for in $field
|
|
* @param string $field field name to search
|
|
*
|
|
* @return callable
|
|
*/
|
|
public static function inStringArray(array $ids, $field)
|
|
{
|
|
static $counter = 0;
|
|
|
|
return function (QueryBuilder $qb) use ($ids, $field, &$counter) {
|
|
$paramName = 'inStringArray_'.$counter++;
|
|
$qb->setParameter($paramName, $ids, Connection::PARAM_STR_ARRAY);
|
|
|
|
return "{$field} IN (:{$paramName})";
|
|
};
|
|
}
|
|
|
|
/**
|
|
* @param array $mapping field => value mapping
|
|
* @param string $operator operator used for joining
|
|
*
|
|
* @return callable
|
|
*/
|
|
public static function equals($mapping, $operator = 'AND')
|
|
{
|
|
static $counter = 0;
|
|
|
|
return function (QueryBuilder $qb) use ($mapping, $operator, &$counter) {
|
|
$parts = [];
|
|
foreach ($mapping as $field => $value) {
|
|
$paramName = 'equals_'.$counter++;
|
|
$qb->setParameter($paramName, $value);
|
|
$parts[] = " {$field} = :{$paramName} ";
|
|
}
|
|
|
|
return join($operator, $parts);
|
|
};
|
|
}
|
|
|
|
/**
|
|
* @param array $mapping field => value mapping
|
|
* @param string $operator operator used for joining
|
|
*
|
|
* @return callable
|
|
*/
|
|
public static function like($mapping, $operator = 'AND')
|
|
{
|
|
static $counter = 0;
|
|
|
|
return function (QueryBuilder $qb) use ($mapping, $operator, &$counter) {
|
|
$parts = [];
|
|
foreach ($mapping as $field => $value) {
|
|
$paramName = 'like_'.$counter++;
|
|
$qb->setParameter($paramName, $value);
|
|
$parts[] = " {$field} LIKE :{$paramName} ";
|
|
}
|
|
|
|
return join($operator, $parts);
|
|
};
|
|
}
|
|
|
|
/**
|
|
* @param array $mapping field => value mapping
|
|
* @param string $operator operator used for joining
|
|
*
|
|
* @return callable
|
|
*/
|
|
public static function equalsNullable($mapping, $operator = 'AND')
|
|
{
|
|
static $counter = 0;
|
|
|
|
return function (QueryBuilder $qb) use ($mapping, $operator, &$counter) {
|
|
$parts = [];
|
|
foreach ($mapping as $field => $value) {
|
|
$paramName = 'equalsNullable_'.$counter++;
|
|
if (is_null($value)) {
|
|
$parts[] = " {$field} IS NULL ";
|
|
} else {
|
|
$qb->setParameter($paramName, $value);
|
|
$parts[] = " {$field} = :{$paramName} ";
|
|
}
|
|
}
|
|
|
|
return join($operator, $parts);
|
|
};
|
|
}
|
|
|
|
public static function not($operand)
|
|
{
|
|
return function (QueryBuilder $qb) use ($operand) {
|
|
$expression = $qb->evaluateClosures([$operand])[0];
|
|
|
|
if (is_null($expression)) {
|
|
return null;
|
|
}
|
|
|
|
return "NOT ({$expression})";
|
|
};
|
|
}
|
|
|
|
public static function isNull($field)
|
|
{
|
|
return function (QueryBuilder $qb) use ($field) {
|
|
return $qb->expr()->isNull($field);
|
|
};
|
|
}
|
|
|
|
public static function isNotNull($field)
|
|
{
|
|
return function (QueryBuilder $qb) use ($field) {
|
|
return $qb->expr()->isNotNull($field);
|
|
};
|
|
}
|
|
|
|
public static function notOrNull($operand, $nullableField)
|
|
{
|
|
return function (QueryBuilder $qb) use ($operand, $nullableField) {
|
|
return $qb->evaluateClosures([Operator::not($operand)])[0].' OR '.$nullableField.' IS NULL';
|
|
};
|
|
}
|
|
|
|
public static function equalsToOrNullable($fieldName1, $fieldName2)
|
|
{
|
|
return "({$fieldName1} = {$fieldName2} OR ({$fieldName1} IS NULL AND {$fieldName2} IS NULL))";
|
|
}
|
|
|
|
public static function coalesce($fieldName1, $fieldName2 = null)
|
|
{
|
|
$columns = array_filter(func_get_args());
|
|
|
|
// nothing to coalesce, return column without coalesce
|
|
if (count($columns) === 1) {
|
|
return reset($columns);
|
|
}
|
|
|
|
$columns = join(',', $columns);
|
|
|
|
return "COALESCE({$columns})";
|
|
}
|
|
|
|
/**
|
|
* @param QueryBuilder[] $queryBuilders
|
|
*/
|
|
public static function union(array $queryBuilders): callable
|
|
{
|
|
return function (QueryBuilder $qb) use ($queryBuilders) {
|
|
$closures = [];
|
|
foreach ($queryBuilders as $subQb) {
|
|
$closures[] = Operator::subquery($subQb);
|
|
}
|
|
|
|
return '('.implode(' UNION ', $qb->evaluateClosures($closures)).')';
|
|
};
|
|
}
|
|
|
|
public static function subquery(QueryBuilder $subQb): callable
|
|
{
|
|
return function (QueryBuilder $qb) use ($subQb) {
|
|
$qb->addQueryBuilderParameters($subQb);
|
|
|
|
return '('.$subQb->getSQL().')';
|
|
};
|
|
}
|
|
|
|
public static function exists(QueryBuilder $qbToTest)
|
|
{
|
|
return function (QueryBuilder $qb) use ($qbToTest) {
|
|
$qb->addParameters($qbToTest->getParameters(), $qbToTest->getParameterTypes());
|
|
|
|
return "EXISTS ({$qbToTest->getSQL()})";
|
|
};
|
|
}
|
|
|
|
// Zbpůsobí, aby se query vůbec nevykonala. Je třeba zajistit, že to parent handluje.
|
|
// Nebylo by lepší to vyřešit memberem na QB, který by vracel prázdný statement?
|
|
public static function forceEmptyResult()
|
|
{
|
|
return function (QueryBuilder $qb) {
|
|
throw new ForceEmptyResultException();
|
|
};
|
|
}
|
|
}
|