public function comparisonData()
{
$cb = new CriteriaBuilder();
$qb = new QueryBuilder();
return array(array($cb->eq('field', 'value'), $qb->eq('o.field', ':field'), new Parameter('field', 'value')), array($cb->neq('field', 'value'), $qb->neq('o.field', ':field'), new Parameter('field', 'value')), array($cb->eq('field', null), $qb->isNull('o.field')), array($cb->neq('field', null), $qb->isNotNull('o.field')), array($cb->isNull('field'), $qb->isNull('o.field')), array($cb->gt('field', 'value'), $qb->gt('o.field', ':field'), new Parameter('field', 'value')), array($cb->gte('field', 'value'), $qb->gte('o.field', ':field'), new Parameter('field', 'value')), array($cb->lt('field', 'value'), $qb->lt('o.field', ':field'), new Parameter('field', 'value')), array($cb->lte('field', 'value'), $qb->lte('o.field', ':field'), new Parameter('field', 'value')), array($cb->in('field', array('value')), $qb->in('o.field', ':field'), new Parameter('field', array('value'))), array($cb->notIn('field', array('value')), $qb->notIn('o.field', ':field'), new Parameter('field', array('value'))), array($cb->eq('object.field', 'value'), $qb->eq('o.object.field', ':object_field'), new Parameter('object_field', 'value')));
}
/**
* {@inheritdoc}
*/
protected function doWalkComparison($fieldName, $operator, $placeholder)
{
switch ($operator) {
case Comparison::IN:
return $this->expr->in($fieldName, $placeholder);
case Comparison::NIN:
return $this->expr->notIn($fieldName, $placeholder);
case Comparison::EQ:
case Comparison::IS:
if ($placeholder === null) {
return $this->expr->isNull($fieldName);
}
return $this->expr->eq($fieldName, $placeholder);
case Comparison::NEQ:
if ($placeholder === null) {
return $this->expr->isNotNull($fieldName);
}
return $this->expr->neq($fieldName, $placeholder);
case Comparison::CONTAINS:
return $this->expr->like($fieldName, $placeholder);
default:
$operator = self::convertComparisonOperator($operator);
if ($operator) {
return new Expr\Comparison($fieldName, $operator, $placeholder);
}
throw new \RuntimeException("Unknown comparison operator: " . $operator);
}
}
function it_adds_an_empty_filter_on_a_field_in_the_query($qb, Expr $expr)
{
$qb->getRootAlias()->willReturn('f');
$qb->leftJoin('f.family', Argument::any())->willReturn($qb);
$qb->andWhere('filterfamily.id IS NULL')->willReturn($qb);
$expr->isNull(Argument::any())->willReturn('filterfamily.id IS NULL');
$qb->expr()->willReturn($expr);
$this->addFieldFilter('family', 'EMPTY', null);
}
function it_adds_an_empty_filter_on_a_field_in_the_query($qb, Expr $expr)
{
$qb->getRootAlias()->willReturn('f');
$qb->leftJoin('f.family', 'filterfamily')->willReturn($qb);
$qb->andWhere('filterfamily.id IS NULL')->willReturn($qb);
$expr->isNull('filterfamily.id')->willReturn('filterfamily.id IS NULL');
$qb->expr()->willReturn($expr);
$this->addFieldFilter('family', 'IN', ['empty']);
}
function it_adds_a_not_between_filter_on_an_field_in_the_query(QueryBuilder $qb, Expr $expr)
{
$qb->andWhere("p.release_date < '2014-03-15' OR p.release_date > '2014-03-20 23:59:59'")->willReturn($qb);
$expr->orX("p.release_date < '2014-03-15'", "p.release_date > '2014-03-20 23:59:59'")->willReturn("p.release_date < '2014-03-15' OR p.release_date > '2014-03-20 23:59:59'");
$qb->expr()->willReturn($expr);
$expr->lt('p.release_date', '2014-03-15')->willReturn("p.release_date < '2014-03-15'");
$expr->gt('p.release_date', '2014-03-20 23:59:59')->willReturn("p.release_date > '2014-03-20 23:59:59'");
$expr->literal('2014-03-15')->willReturn('2014-03-15');
$expr->literal('2014-03-20 23:59:59')->willReturn('2014-03-20 23:59:59');
$this->addFieldFilter('release_date', 'NOT BETWEEN', array('from' => '2014-03-15', 'to' => '2014-03-20'));
}
/**
* @test
*/
public function thatFilters()
{
$a = new EntityStub();
$b = new EntityStub();
$a->name = "DUMMY_NAME";
$a->id = 1;
$b->name = "NOT_DUMMY_NAME";
$b->id = 2;
$entities = array($a, $b);
$pagingConfig = new FilterPagingProperties();
$conditions = array(array('name', 'eq', 'DUMMY_NAME'));
$whereExpr = new \Doctrine\ORM\Query\Expr\Comparison('e.name', 'eq', "'DUMMY_NAME'");
$this->em->expects($this->once())->method('createQueryBuilder')->will($this->returnValue($this->queryBuilder));
$this->queryBuilder->expects($this->once())->method('select')->with($this->equalTo('e'))->will($this->returnValue($this->queryBuilder));
$this->queryBuilder->expects($this->once())->method('from')->with($this->equalTo($this->classMetadata->name), $this->equalTo('e'))->will($this->returnValue($this->queryBuilder));
$this->queryBuilder->expects($this->atLeastOnce())->method('andWhere')->with($this->equalTo($whereExpr))->will($this->returnValue($this->queryBuilder));
$this->queryBuilder->expects($this->once())->method('addOrderBy')->with($this->equalTo('e.id'), $this->equalTo($pagingConfig->getOrder()))->will($this->returnValue($this->queryBuilder));
$this->queryBuilder->expects($this->once())->method('setFirstResult')->with($this->equalTo(0))->will($this->returnValue($this->queryBuilder));
$this->queryBuilder->expects($this->once())->method('setMaxResults')->with($this->equalTo($pagingConfig->getLimit()))->will($this->returnValue($this->queryBuilder));
$this->queryBuilder->expects($this->once())->method('getQuery')->will($this->returnValue($this->filterQuery));
$this->queryBuilder->expects($this->any())->method('expr')->will($this->returnValue($this->expressionBuilder));
$this->em->expects($this->any())->method('getExpressionBuilder')->will($this->returnValue($this->expressionBuilder));
$this->expressionBuilder->expects($this->atLeastOnce())->method('literal')->with($this->equalTo('DUMMY_NAME'))->will($this->returnValue("'DUMMY_NAME'"));
$this->expressionBuilder->expects($this->atLeastOnce())->method('eq')->with($this->equalTo('e.name'), $this->equalTo("'DUMMY_NAME'"))->will($this->returnValue($whereExpr));
$this->em->expects($this->atLeastOnce())->method('newHydrator')->with($this->equalTo(Query::HYDRATE_OBJECT))->will($this->returnValue($this->objectHydrator));
$this->objectHydrator->expects($this->atLeastOnce())->method('hydrateAll')->will($this->returnValue(array($a)));
$filteredEntities = $this->repository->filter($conditions, $pagingConfig);
$this->assertNotNull($filteredEntities);
$this->assertTrue(is_array($filteredEntities));
$this->assertNotEmpty($filteredEntities);
$this->assertEquals($a, $filteredEntities[0]);
}
/**
* {@inheritDoc}
*/
public function walkComparison(Comparison $comparison)
{
$parameterName = str_replace('.', '_', $comparison->getField());
$parameter = new Parameter($parameterName, $this->walkValue($comparison->getValue()));
$placeholder = ':' . $parameterName;
switch ($comparison->getOperator()) {
case Comparison::IN:
$this->parameters[] = $parameter;
return $this->expr->in($this->rootAlias . '.' . $comparison->getField(), $placeholder);
case Comparison::NIN:
$this->parameters[] = $parameter;
return $this->expr->notIn($this->rootAlias . '.' . $comparison->getField(), $placeholder);
case Comparison::EQ:
case Comparison::IS:
if ($this->walkValue($comparison->getValue()) === null) {
return $this->expr->isNull($this->rootAlias . '.' . $comparison->getField());
}
$this->parameters[] = $parameter;
return $this->expr->eq($this->rootAlias . '.' . $comparison->getField(), $placeholder);
case Comparison::NEQ:
if ($this->walkValue($comparison->getValue()) === null) {
return $this->expr->isNotNull($this->rootAlias . '.' . $comparison->getField());
}
$this->parameters[] = $parameter;
return $this->expr->neq($this->rootAlias . '.' . $comparison->getField(), $placeholder);
default:
$operator = self::convertComparisonOperator($comparison->getOperator());
if ($operator) {
$this->parameters[] = $parameter;
return new Expr\Comparison($this->rootAlias . '.' . $comparison->getField(), $operator, $placeholder);
}
throw new \RuntimeException("Unknown comparison operator: " . $comparison->getOperator());
}
}
function it_does_not_try_to_join_attributes_when_finding_full_products_with_empty_attributes($em, QueryBuilder $queryBuilder, Expr $expr, AbstractQuery $query)
{
$expr->in('p.id', [42])->shouldBeCalled()->willReturn('product where clause');
$expr->in('a.id', Argument::any())->shouldNotBeCalled();
$queryBuilder->select(Argument::any())->shouldBeCalled()->willReturn($queryBuilder);
$queryBuilder->addSelect(Argument::any())->shouldBeCalled()->willReturn($queryBuilder);
$queryBuilder->from(Argument::any(), Argument::any())->shouldBeCalled()->willReturn($queryBuilder);
$queryBuilder->leftJoin(Argument::any(), Argument::any())->shouldBeCalled()->willReturn($queryBuilder);
$queryBuilder->where('product where clause')->shouldBeCalled()->willReturn($queryBuilder);
$queryBuilder->andWhere(Argument::any())->shouldNotBeCalled();
$em->createQueryBuilder()->willReturn($queryBuilder);
$query->execute()->shouldBeCalled();
$queryBuilder->expr()->willReturn($expr);
$queryBuilder->getQuery()->willReturn($query);
$this->getFullProducts([42]);
}
/**
* {@inheritDoc}
*/
public function walkComparison(Comparison $comparison)
{
if (!isset($this->queryAliases[0])) {
throw new QueryException('No aliases are set before invoking walkComparison().');
}
$field = $this->queryAliases[0] . '.' . $comparison->getField();
foreach ($this->queryAliases as $alias) {
if (strpos($comparison->getField() . '.', $alias . '.') === 0) {
$field = $comparison->getField();
break;
}
}
$parameterName = str_replace('.', '_', $comparison->getField());
foreach ($this->parameters as $parameter) {
if ($parameter->getName() === $parameterName) {
$parameterName .= '_' . count($this->parameters);
break;
}
}
$parameter = new Parameter($parameterName, $this->walkValue($comparison->getValue()));
$placeholder = ':' . $parameterName;
switch ($comparison->getOperator()) {
case Comparison::IN:
$this->parameters[] = $parameter;
return $this->expr->in($field, $placeholder);
case Comparison::NIN:
$this->parameters[] = $parameter;
return $this->expr->notIn($field, $placeholder);
case Comparison::EQ:
case Comparison::IS:
if ($this->walkValue($comparison->getValue()) === null) {
return $this->expr->isNull($field);
}
$this->parameters[] = $parameter;
return $this->expr->eq($field, $placeholder);
case Comparison::NEQ:
if ($this->walkValue($comparison->getValue()) === null) {
return $this->expr->isNotNull($field);
}
$this->parameters[] = $parameter;
return $this->expr->neq($field, $placeholder);
case Comparison::CONTAINS:
$parameter->setValue('%' . $parameter->getValue() . '%', $parameter->getType());
$this->parameters[] = $parameter;
return $this->expr->like($field, $placeholder);
default:
$operator = self::convertComparisonOperator($comparison->getOperator());
if ($operator) {
$this->parameters[] = $parameter;
return new Expr\Comparison($field, $operator, $placeholder);
}
throw new \RuntimeException("Unknown comparison operator: " . $comparison->getOperator());
}
}
/**
* @test
*/
public function testSearch()
{
$a = new EntityStub();
$b = new EntityStub();
$a->name = "DUMMY_NAME";
$a->status = "new";
$a->id = 1;
$b->name = "NOT_DUMMY_NAME";
$b->status = "closed";
$b->id = 2;
$entities = array($a, $b);
$pagingConfig = new FilterPagingProperties();
$querySearch = 'NAM';
$conditions = array(array('status', 'eq', 'new'));
$this->em->expects($this->once())->method('createQueryBuilder')->will($this->returnValue($this->queryBuilder));
$this->queryBuilder->expects($this->once())->method('select')->with($this->equalTo('e'))->will($this->returnValue($this->queryBuilder));
$this->queryBuilder->expects($this->once())->method('from')->with($this->equalTo($this->classMetadata->name), $this->equalTo('e'))->will($this->returnValue($this->queryBuilder));
$whereExpr = new \Doctrine\ORM\Query\Expr\Comparison('e.status', 'eq', 'new');
$likeExprSubject = new \Doctrine\ORM\Query\Expr\Comparison('e.subject', 'like', '%NAM%');
$likeExprDescription = new \Doctrine\ORM\Query\Expr\Comparison('e.description', 'like', '%NAM%');
$whereExprOrx = new \Doctrine\ORM\Query\Expr\Orx($likeExprSubject, $likeExprDescription);
$this->queryBuilder->expects($this->atLeastOnce())->method('andWhere')->with($this->logicalOr($this->equalTo($whereExpr), $this->equalTo($whereExprOrx)))->will($this->returnValue($this->queryBuilder));
$this->queryBuilder->expects($this->once())->method('addOrderBy')->with($this->equalTo('e.id'), $this->equalTo($pagingConfig->getOrder()))->will($this->returnValue($this->queryBuilder));
$this->queryBuilder->expects($this->once())->method('setFirstResult')->with($this->equalTo(0))->will($this->returnValue($this->queryBuilder));
$this->queryBuilder->expects($this->once())->method('setMaxResults')->with($this->equalTo($pagingConfig->getLimit()))->will($this->returnValue($this->queryBuilder));
$this->queryBuilder->expects($this->once())->method('getQuery')->will($this->returnValue($this->searchQuery));
$this->queryBuilder->expects($this->any())->method('expr')->will($this->returnValue($this->expressionBuilder));
$this->em->expects($this->any())->method('getExpressionBuilder')->will($this->returnValue($this->expressionBuilder));
$this->expressionBuilder->expects($this->atLeast(2))->method('literal')->with($this->logicalOr($this->equalTo('new'), $this->equalTo('%' . $querySearch . '%')))->will($this->returnCallback(array($this, 'literalCallback')));
$this->expressionBuilder->expects($this->atLeastOnce())->method('eq')->with($this->equalTo('e.status'), $this->equalTo('new'))->will($this->returnValue($whereExpr));
$this->expressionBuilder->expects($this->atLeastOnce())->method('Orx')->with($this->anything(), $this->anything())->will($this->returnValue($whereExprOrx));
$this->em->expects($this->atLeastOnce())->method('newHydrator')->with($this->equalTo(Query::HYDRATE_OBJECT))->will($this->returnValue($this->objectHydrator));
$this->objectHydrator->expects($this->atLeastOnce())->method('hydrateAll')->will($this->returnValue(array($a)));
$searchedEntities = $this->repository->search($querySearch, $conditions, $pagingConfig);
$this->assertNotNull($searchedEntities);
$this->assertTrue(is_array($searchedEntities));
$this->assertNotEmpty($searchedEntities);
$this->assertEquals($a, $searchedEntities[0]);
}
protected function getParts($qb, $where, &$params = array())
{
if (array_key_exists('mode', $where) && $where['mode'] == 'or') {
$whereExp = $qb->expr()->orX();
} else {
$whereExp = $qb->expr()->andX();
}
unset($where['mode']);
// unset to allow flat iteration for simpler structures
// index for the parameters
$i = count($params);
if (array_key_exists('expressions', $where)) {
foreach ($where['expressions'] as $expression) {
$part = $this->getParts($qb, $expression, $params);
$whereExp->add($part);
}
unset($where['expressions']);
// unset to allow flat iteration for simpler structures
}
// set fields to iterate over if no fields property assume using remaining elements in $where
$fields = array_key_exists('fields', $where) ? $where['fields'] : $where;
// loop through all the clauses supplied
foreach ($fields as $col => $val) {
if (is_array($val) && (!isset($val['value']) || is_string($val['value']) && strlen($val['value']) == 0) || is_string($val) && (!$val || strlen($val) == 0)) {
// skip if invalid value;
continue;
}
// check if we've been provided with an operator as well as a value
if (!is_array($val)) {
$operator = Expr\Comparison::EQ;
$val = $val;
} elseif (count($val) == 1) {
$operator = Expr\Comparison::EQ;
$val = end($val);
} else {
$operator = isset($val['operator']) ? $val['operator'] : Expr\Comparison::EQ;
$val = array_key_exists('value', $val) ? $val['value'] : array();
}
// set the alias to the default
$alias = $this->alias;
// if col relates to a relation i.e. Role.id
// then perform a join and set up the alias and column names
if (strpos($col, '.') !== false) {
$parts = explode('.', $col);
$col = array_pop($parts);
$par = $this->alias;
foreach ($parts as $rel) {
$alias = strtolower($rel);
$jt = new Expr\Join(Expr\Join::LEFT_JOIN, $par . '.' . $rel, $alias);
if (!strpos($qb->getDql(), $jt->__toString()) !== false) {
$qb->leftJoin($par . '.' . $rel, $alias);
}
$par = $alias;
}
}
if ($val instanceof Expr\Base) {
$whereExp->add($val);
} else {
// process sets a little differently
if (!is_array($val)) {
$val = array($val);
}
switch (strtolower($operator)) {
case 'regexp':
$whereExp->add("REGEXP(" . $alias . '.' . $col . ",'" . $val[0] . "') = 1");
break;
case 'between':
if (count($val) == 2) {
// $value should now be an array with 2 values
$expr = new Expr();
$from = is_int($val[0]) ? $val[0] : "'" . $val[0] . "'";
$to = is_int($val[1]) ? $val[1] : "'" . $val[1] . "'";
$stmt = $expr->between($alias . '.' . $col, $from, $to);
$whereExp->add($stmt);
}
break;
case 'is':
$expr = new Expr();
$method = 'is' . ucfirst($val[0]);
if (method_exists($expr, $method)) {
$stmt = $expr->{$method}($alias . '.' . $col);
$whereExp->add($stmt);
}
break;
default:
// this holds the subquery for this field, each component being an OR
$subWhereExp = $qb->expr()->orX();
foreach ($val as $value) {
if ($value == null) {
$cmpValue = 'NULL';
} else {
$cmpValue = '?' . $i;
// wrap IN/NOT IN values with parenthesis
if ($operator == 'in' || $operator == 'not in') {
$cmpValue = '(' . trim($cmpValue, ')') . ')';
}
// wrap LIKE values
if ($operator == 'like') {
$value = '%' . trim($value, '%') . '%';
}
// add the parameter value into the parameters stack
$params[$i] = $value;
$i++;
}
$comparison = new Expr\Comparison($alias . '.' . $col, $operator, $cmpValue);
$subWhereExp->add($comparison);
}
// add in the subquery as an AND
$whereExp->add($subWhereExp);
break;
}
}
}
return $whereExp;
}
function it_adds_a_not_in_filter_to_the_query($qb, $attrValidatorHelper, AttributeInterface $attribute, EntityManager $em, QueryBuilder $notInQb, Expr $expr, Expr\Func $notInFunc, Expr\Func $inFunc, Expr\Func $whereFunc)
{
$attrValidatorHelper->validateLocale($attribute, Argument::any())->shouldBeCalled();
$attrValidatorHelper->validateScope($attribute, Argument::any())->shouldBeCalled();
$attribute->getId()->willReturn(42);
$attribute->isLocalizable()->willReturn(false);
$attribute->isScopable()->willReturn(false);
$attribute->getBackendType()->willReturn('options');
$attribute->getCode()->willReturn('options_code');
$qb->getRootAlias()->willReturn('r');
$expr->notIn(Argument::containingString('filterOoptions_code'), [10, 12])->shouldBeCalled()->willReturn($notInFunc);
$qb->innerJoin(Argument::cetera())->willReturn($qb);
$qb->getEntityManager()->willReturn($em);
$qb->getRootEntities()->willReturn(['ProductClassName']);
$em->createQueryBuilder()->willReturn($notInQb);
$notInQb->select(Argument::containingString('.id'))->shouldBeCalled()->willReturn($notInQb);
$notInQb->from('ProductClassName', Argument::any(), Argument::containingString('.id'))->shouldBeCalled()->willReturn($notInQb);
$notInQb->getRootAlias()->willReturn('ep');
$notInQb->innerJoin('ep.values', Argument::containingString('filteroptions_code'), 'WITH', Argument::any())->shouldBeCalled()->willReturn($notInQb);
$notInQb->innerJoin(Argument::containingString('filteroptions_code'), Argument::containingString('filterOoptions_code'))->shouldBeCalled()->willReturn($notInQb);
$notInQb->expr()->willReturn($expr);
$expr->in(Argument::containingString('.id'), [10, 12])->shouldBeCalled()->willReturn($inFunc);
$notInQb->where($inFunc)->shouldBeCalled();
$notInQb->getDQL()->willReturn('excluded products DQL');
$qb->expr()->willReturn($expr);
$expr->notIn('r.id', 'excluded products DQL')->shouldBeCalled()->willReturn($whereFunc);
$qb->andWhere($whereFunc)->shouldBeCalled();
$this->addAttributeFilter($attribute, 'NOT IN', [10, 12], null, null, ['field' => 'options_code.id']);
}
/**
* {@inheritdoc}
*/
public function notBetween($value, $x, $y)
{
return $this->expr->not($this->between($value, $x, $y));
}
public function testAddNull()
{
$andExpr = $this->_expr->andx();
$andExpr->add(null);
$this->assertEquals(0, $andExpr->count());
}
/**
* Build where statement and add to the query builder.
*
* @param \Doctrine\Orm\QueryBuilder $qb
* @param mixed $where
* @return $this
*/
protected function addWhere($qb, $where)
{
// process the $where
if (is_string($where)) {
// straight DQL string
$qb->andWhere($where);
} elseif (is_array($where) && count($where)) {
// create where expression
$whereExp = $qb->expr()->andx();
$params = array();
// index for the parameters
$i = 0;
// loop through all the clauses supplied
foreach ($where as $col => $val) {
if (is_array($val) && (!isset($val['value']) || is_string($val['value']) && strlen($val['value']) == 0) || is_string($val) && (!$val || strlen($val) == 0)) {
// skip if invalid value;
continue;
}
// check if we've been provided with an operator as well as a value
if (!is_array($val)) {
$operator = Expr\Comparison::EQ;
$val = $val;
} elseif (count($val) == 1) {
$operator = Expr\Comparison::EQ;
$val = end($val);
} else {
$operator = isset($val['operator']) ? $val['operator'] : Expr\Comparison::EQ;
$val = array_key_exists('value', $val) ? $val['value'] : array();
}
// set the alias to the default
$alias = $this->alias;
// if col relates to a relation i.e. Role.id
// then perform a join and set up the alias and column names
if (strpos($col, '.') !== false) {
$parts = explode('.', $col);
$col = array_pop($parts);
$par = $this->alias;
foreach ($parts as $rel) {
$alias = strtolower($rel);
$jt = new Expr\Join(Expr\Join::LEFT_JOIN, $par . '.' . $rel, $alias);
if (!strpos($qb->getDql(), $jt->__toString()) !== false) {
$qb->leftJoin($par . '.' . $rel, $alias);
}
$par = $alias;
}
}
// process sets a little differently
if (!is_array($val)) {
$val = array($val);
}
if ($operator == 'regexp') {
$whereExp->add("REGEXP(" . $alias . '.' . $col . ",'" . $val[0] . "') = 1");
} else {
if ($operator == 'between') {
if (count($val) == 2) {
// $value should now be an array with 2 values
$expr = new Expr();
$from = is_int($val[0]) ? $val[0] : "'" . $val[0] . "'";
$to = is_int($val[1]) ? $val[1] : "'" . $val[1] . "'";
$stmt = $expr->between($alias . '.' . $col, $from, $to);
$whereExp->add($stmt);
}
} else {
if ($operator == 'is') {
$expr = new Expr();
$method = 'is' . ucfirst($val[0]);
if (method_exists($expr, $method)) {
$stmt = $expr->{$method}($alias . '.' . $col);
$whereExp->add($stmt);
}
} else {
// this holds the subquery for this field, each component being an OR
$subWhereExp = $qb->expr()->orX();
foreach ($val as $value) {
if ($value == null) {
$cmpValue = 'NULL';
} else {
$cmpValue = '?' . $i;
// wrap LIKE values
if ($operator == 'like') {
$value = '%' . trim($value, '%') . '%';
}
// add the parameter value into the parameters stack
$params[$i] = $value;
$i++;
}
$comparison = new Expr\Comparison($alias . '.' . $col, $operator, $cmpValue);
$subWhereExp->add($comparison);
}
// add in the subquery as an AND
$whereExp->add($subWhereExp);
}
}
}
}
// only add where expression if actually has parts
if (count($whereExp->getParts())) {
$qb->where($whereExp);
}
// set the params from the where clause above
$qb->setParameters($params);
}
return $this;
}
/**
* {@inheritdoc}
*/
public function trim($x)
{
return $this->expr->trim($x);
}
/**
* @param string $x
* @param mixed $y
*
* @return \Doctrine\ORM\Query\Expr\Comparison
* @author Dominic Tubach <*****@*****.**>
*/
public function notLike($x, $y)
{
return parent::notLike($x, $y . " ESCAPE '\\'");
}
function it_adds_a_not_between_filter_on_an_field_in_the_query(QueryBuilder $qb, Expr $expr)
{
$qb->andWhere("p.release_date < '2014-03-15' OR p.release_date > '2014-03-20 23:59:59'")->shouldBeCalledTimes(2)->willReturn($qb);
$expr->orX("p.release_date < '2014-03-15'", "p.release_date > '2014-03-20 23:59:59'")->shouldBeCalledTimes(2)->willReturn("p.release_date < '2014-03-15' OR p.release_date > '2014-03-20 23:59:59'");
$qb->expr()->willReturn($expr);
$expr->lt('p.release_date', '2014-03-15')->shouldBeCalledTimes(2)->willReturn("p.release_date < '2014-03-15'");
$expr->gt('p.release_date', '2014-03-20 23:59:59')->shouldBeCalledTimes(2)->willReturn("p.release_date > '2014-03-20 23:59:59'");
$expr->literal('2014-03-15')->shouldBeCalledTimes(2)->willReturn('2014-03-15');
$expr->literal('2014-03-20 23:59:59')->shouldBeCalledTimes(2)->willReturn('2014-03-20 23:59:59');
$this->addFieldFilter('release_date', 'NOT BETWEEN', ['2014-03-15', '2014-03-20']);
$this->addFieldFilter('release_date', 'NOT BETWEEN', [new \Datetime('2014-03-15'), new \Datetime('2014-03-20')]);
}
/**
* {@inheritdoc}
*/
public function exists($x)
{
return $this->expr->exists($x);
}
/**
* Builds the orm expression.
*
* @param string $property
* @param string $operator
* @param string $parameter
* @return Expr\Comparison|Expr\Func
*/
public static function buildExpression($property, $operator, $parameter = null)
{
self::isValid($operator, true);
$expr = new Expr();
switch (intval($operator)) {
case self::NOT_EQUAL:
return $expr->neq($property, $parameter);
case self::LOWER_THAN:
return $expr->lt($property, $parameter);
case self::LOWER_THAN_OR_EQUAL:
return $expr->lte($property, $parameter);
case self::GREATER_THAN:
return $expr->gt($property, $parameter);
case self::GREATER_THAN_OR_EQUAL:
return $expr->gte($property, $parameter);
case self::IN:
return $expr->in($property, $parameter);
case self::NOT_IN:
return $expr->notIn($property, $parameter);
case self::LIKE:
return $expr->like($property, $parameter);
case self::NOT_LIKE:
return $expr->notLike($property, $parameter);
case self::START_WITH:
return $expr->like($property, $parameter);
case self::NOT_START_WITH:
return $expr->notLike($property, $parameter);
case self::END_WITH:
return $expr->like($property, $parameter);
case self::NOT_END_WITH:
return $expr->notLike($property, $parameter);
case self::IS_NULL:
return $expr->isNull($property);
case self::IS_NOT_NULL:
return $expr->isNotNull($property);
default:
return $expr->eq($property, $parameter);
}
}
/**
* @param $field
* @param $value
* @return \Doctrine\ORM\Query\Expr\Comparison
*/
public function likeRight($field, $value)
{
return parent::like($field, $value);
}
public function testNotBetween()
{
$this->expr->expects($this->once())->method('between')->with($this->identicalTo($value = 'property'), $this->identicalTo($x = 'placeholder1'), $this->identicalTo($y = 'placeholder2'))->will($this->returnValue($betweenExpression = 'between_expression'));
$this->expr->expects($this->once())->method('not')->with($this->identicalTo($betweenExpression))->will($this->returnValue($expression = 'expression'));
$this->assertSame($expression, $this->builder->notBetween($value, $x, $y));
}
/**
* @param DatagridFilter $filter
* @throws \InvalidArgumentException
*/
public function applyFilter(DatagridFilter $filter)
{
$qb = $this->getQueryBuilder();
$expr = new Expr();
$column = $filter->getColumn();
$colString = $column->getSelectPart1();
if ($column->getSelectPart2() != '') {
$colString .= '.' . $column->getSelectPart2();
}
if ($column instanceof Column\Select && $column->hasFilterSelectExpression()) {
$colString = sprintf($column->getFilterSelectExpression(), $colString);
}
$values = $filter->getValues();
$wheres = [];
foreach ($values as $key => $value) {
$valueParameterName = ':' . str_replace('.', '', $column->getUniqueId() . $key);
switch ($filter->getOperator()) {
case DatagridFilter::LIKE:
$wheres[] = $expr->like($colString, $valueParameterName);
$qb->setParameter($valueParameterName, '%' . $value . '%');
break;
case DatagridFilter::LIKE_LEFT:
$wheres[] = $expr->like($colString, $valueParameterName);
$qb->setParameter($valueParameterName, '%' . $value);
break;
case DatagridFilter::LIKE_RIGHT:
$wheres[] = $expr->like($colString, $valueParameterName);
$qb->setParameter($valueParameterName, $value . '%');
break;
case DatagridFilter::NOT_LIKE:
$wheres[] = $expr->notLike($colString, $valueParameterName);
$qb->setParameter($valueParameterName, '%' . $value . '%');
break;
case DatagridFilter::NOT_LIKE_LEFT:
$wheres[] = $expr->notLike($colString, $valueParameterName);
$qb->setParameter($valueParameterName, '%' . $value);
break;
case DatagridFilter::NOT_LIKE_RIGHT:
$wheres[] = $expr->notLike($colString, $valueParameterName);
$qb->setParameter($valueParameterName, $value . '%');
break;
case DatagridFilter::EQUAL:
$wheres[] = $expr->eq($colString, $valueParameterName);
$qb->setParameter($valueParameterName, $value);
break;
case DatagridFilter::NOT_EQUAL:
$wheres[] = $expr->neq($colString, $valueParameterName);
$qb->setParameter($valueParameterName, $value);
break;
case DatagridFilter::GREATER_EQUAL:
$wheres[] = $expr->gte($colString, $valueParameterName);
$qb->setParameter($valueParameterName, $value);
break;
case DatagridFilter::GREATER:
$wheres[] = $expr->gt($colString, $valueParameterName);
$qb->setParameter($valueParameterName, $value);
break;
case DatagridFilter::LESS_EQUAL:
$wheres[] = $expr->lte($colString, $valueParameterName);
$qb->setParameter($valueParameterName, $value);
break;
case DatagridFilter::LESS:
$wheres[] = $expr->lt($colString, $valueParameterName);
$qb->setParameter($valueParameterName, $value);
break;
case DatagridFilter::BETWEEN:
$minParameterName = ':' . str_replace('.', '', $colString . '0');
$maxParameterName = ':' . str_replace('.', '', $colString . '1');
$wheres[] = $expr->between($colString, $minParameterName, $maxParameterName);
$qb->setParameter($minParameterName, $values[0]);
$qb->setParameter($maxParameterName, $values[1]);
break 2;
default:
throw new \InvalidArgumentException('This operator is currently not supported: ' . $filter->getOperator());
break;
}
}
if (count($wheres) > 0) {
$orWhere = $qb->expr()->orX();
$orWhere->addMultiple($wheres);
$qb->andWhere($orWhere);
}
}
