/**
* Taken from https://gist.github.com/jgornick/8671644
*
* Recursively takes the specified criteria and adds too the expression.
*
* The criteria is defined in an array notation where each item in the list
* represents a comparison <fieldName, operator, value>. The operator maps to
* comparison methods located in ExpressionBuilder. The key in the array can
* be used to identify grouping of comparisons.
*
* @example
* $criteria = array(
* 'or' => array(
* array('field1', 'like', '%field1Value%'),
* array('field2', 'like', '%field2Value%')
* ),
* 'and' => array(
* array('field3', 'eq', 3),
* array('field4', 'eq', 'four')
* ),
* array('field5', 'neq', 5)
* );
*
* $qb = new QueryBuilder();
* addCriteria($qb, $qb->expr()->andX(), $criteria);
* echo $qb->getSQL();
*
* // Result:
* // SELECT *
* // FROM tableName
* // WHERE ((field1 LIKE '%field1Value%') OR (field2 LIKE '%field2Value%'))
* // AND ((field3 = '3') AND (field4 = 'four'))
* // AND (field5 <> '5')
*
* @param QueryBuilder $qb
* @param CompositeExpression $expr
* @param array $criteria
*/
static function addCriteria(QueryBuilder $qb, $expr, array $criteria)
{
if (count($criteria)) {
// var_dump($criteria);
foreach ($criteria as $element) {
foreach ($element as $expression => $comparison) {
if (!is_array($comparison)) {
$comparison = $element;
}
if ($expression > 0) {
continue;
}
// echo
// "Elem:".((is_array($element))?print_r($element,true):$element).'--'.
// "Expr:".((is_array($expression))?print_r($expression,true):$expression).'--'.
// "Comp:".((is_array($comparison))?print_r($comparison,true):$comparison).'--'.
// "<br>";
//break;
// echo "<br><br>";
// echo($expression);
// echo(print_r($comparison,true));
// if ($comparison)
if ($expression === 'or') {
$expr->add(Doctrine::addCriteria($qb, $qb->expr()->orX(), $comparison));
} else {
if ($expression === 'and') {
$expr->add(Doctrine::addCriteria($qb, $qb->expr()->andX(), $comparison));
} else {
// print_r($comparison);
if (count($comparison) == 3) {
list($field, $operator, $value) = $comparison;
} elseif (count($comparison) == 2) {
list($field, $operator) = $comparison;
}
if ($operator == 'in') {
$expr->add($qb->expr()->{$operator}($field, $value));
} elseif (in_array($operator, ['isNull', 'isNotNull'])) {
$expr->add($qb->expr()->{$operator}($field));
} else {
$expr->add($qb->expr()->{$operator}($field, $qb->expr()->literal($value)));
}
}
}
}
// echo $qb->getDql()."<br><br>";
}
}
return $expr;
}
/**
* Retrieves the string representation of this composite expression.
*
* @return string
*/
public function __toString()
{
if ($this->count() === 0) {
return '';
}
return parent::__toString();
}
/**
* @param QueryBuilder|\Doctrine\ORM\QueryBuilder $q
* @param $column
* @param $operator
* @param $parameter
* @param $includeIsNull true/false or null to auto determine based on operator
*
* @return mixed
*/
protected function generateFilterExpression($q, $column, $operator, $parameter, $includeIsNull, CompositeExpression $appendTo = null)
{
// in/notIn for dbal will use a raw array
if (!is_array($parameter) && strpos($parameter, ':') !== 0) {
$parameter = ":{$parameter}";
}
if (null === $includeIsNull) {
// Auto determine based on negate operators
$includeIsNull = in_array($operator, ['neq', 'notLike', 'notIn']);
}
if ($includeIsNull) {
$expr = $q->expr()->orX($q->expr()->{$operator}($column, $parameter), $q->expr()->isNull($column));
} else {
$expr = $q->expr()->{$operator}($column, $parameter);
}
if ($appendTo) {
$appendTo->add($expr);
return $appendTo;
}
return $expr;
}
/**
* Gets a list of published entities as an array id => label
*
* @param CompositeExpression $expr Use $factory->getDatabase()->getExpressionBuilder()->andX()
* @param array $parameters Parameters used in $expr
* @param string $labelColumn Column that houses the label
* @param string $valueColumn Column that houses the value
*
* @return array
*/
public function getSimpleList(CompositeExpression $expr = null, array $parameters = array(), $labelColumn = null, $valueColumn = 'id')
{
$q = $this->_em->getConnection()->createQueryBuilder();
$alias = $prefix = $this->getTableAlias();
if (!empty($prefix)) {
$prefix .= '.';
}
$tableName = $this->_em->getClassMetadata($this->getEntityName())->getTableName();
$class = '\\' . $this->getClassName();
$reflection = new \ReflectionClass(new $class());
// Get the label column if necessary
if ($labelColumn == null) {
if ($reflection->hasMethod('getTitle')) {
$labelColumn = 'title';
} else {
$labelColumn = 'name';
}
}
$q->select($prefix . $valueColumn . ' as value, ' . $prefix . $labelColumn . ' as label')->from($tableName, $alias)->orderBy($prefix . $labelColumn);
if ($expr !== null && $expr->count()) {
$q->where($expr);
}
if (!empty($parameters)) {
$q->setParameters($parameters);
}
// Published only
if ($reflection->hasMethod('getIsPublished')) {
$q->andWhere($q->expr()->eq($prefix . 'is_published', ':true'))->setParameter('true', true, 'boolean');
}
return $q->execute()->fetchAll();
}
/**
* Returns the type of this composite expression (AND/OR).
*
* @return string
*/
public function getType()
{
return $this->compositeExpression->getType();
}
/**
* @param QueryBuilder $builder
* @param CompositeExpression $link
* @param ErrorCollection $errors
* @param string $table
* @param string $column
* @param string $method
* @param string|array $params
*
* @return void
*
* @SuppressWarnings(PHPMD.ElseExpression)
*/
protected function applyComparisonMethod(QueryBuilder $builder, CompositeExpression $link, ErrorCollection $errors, $table, $column, $method, $params)
{
// params could be in form of 1 value or array of values
if (is_array($params) === true) {
foreach ($params as $param) {
if (is_scalar($param) === true) {
$param = (string) $builder->createNamedParameter($param);
$link->add($builder->expr()->{$method}($this->getTableColumn($table, $column), $param));
} else {
$this->addInvalidQueryParameterError($errors, $column);
}
}
} elseif (is_scalar($params) === true) {
$param = $builder->createNamedParameter((string) $params);
$link->add($builder->expr()->{$method}($this->getTableColumn($table, $column), $param));
} else {
// parameter is neither array nor string/scalar
$this->addInvalidQueryParameterError($errors, $column);
}
}
/**
* Gets the compiled expression as a string. This will look
* something like `(alias.key = :placeholder)`.
*
* @return string
*/
public function getExpression()
{
return $this->expression->__toString();
}