public function testDefaultOperator()
{
$filter = new Filter();
$this->assertFalse($filter->isColumnFilter());
$filter->setFromColumn($this->column, 'test');
$this->assertEquals($this->column, $filter->getColumn());
$this->assertEquals(Filter::LIKE, $filter->getOperator());
$this->assertEquals('~ *test*', $filter->getDisplayColumnValue());
$this->assertTrue(is_array($filter->getValues()));
$this->assertEquals(array('test'), $filter->getValues());
$this->assertTrue($filter->isColumnFilter());
}
/**
* @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 = array();
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);
}
}
/**
* @param DatagridFilter $filter
* @throws \InvalidArgumentException
*/
public function applyFilter(DatagridFilter $filter)
{
$select = $this->getSelect();
$adapter = $this->getSql()->getAdapter();
$qi = function ($name) use($adapter) {
return $adapter->getPlatform()->quoteIdentifier($name);
};
$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();
$filterSelectOptions = $column->getFilterSelectOptions();
$wheres = array();
if ($filter->getColumn()->getType() instanceof Column\Type\DateTime && $filter->getColumn()->getType()->isDaterangePickerEnabled() === true) {
$where = new Where();
$wheres[] = $where->between($colString, $values[0], $values[1]);
if (count($wheres) > 0) {
$set = new PredicateSet($wheres, PredicateSet::OP_AND);
$select->where->andPredicate($set);
}
} else {
foreach ($values as $value) {
$where = new Where();
switch ($filter->getOperator()) {
case DatagridFilter::LIKE:
$wheres[] = $where->like($colString, '%' . $value . '%');
break;
case DatagridFilter::LIKE_LEFT:
$wheres[] = $where->like($colString, '%' . $value);
break;
case DatagridFilter::LIKE_RIGHT:
$wheres[] = $where->like($colString, $value . '%');
break;
case DatagridFilter::NOT_LIKE:
$wheres[] = $where->literal($qi($colString) . 'NOT LIKE ?', array('%' . $value . '%'));
break;
case DatagridFilter::NOT_LIKE_LEFT:
$wheres[] = $where->literal($qi($colString) . 'NOT LIKE ?', array('%' . $value));
break;
case DatagridFilter::NOT_LIKE_RIGHT:
$wheres[] = $where->literal($qi($colString) . 'NOT LIKE ?', array($value . '%'));
break;
case DatagridFilter::EQUAL:
$wheres[] = $where->equalTo($colString, $value);
break;
case DatagridFilter::NOT_EQUAL:
$wheres[] = $where->notEqualTo($colString, $value);
break;
case DatagridFilter::GREATER_EQUAL:
$wheres[] = $where->greaterThanOrEqualTo($colString, $value);
break;
case DatagridFilter::GREATER:
$wheres[] = $where->greaterThan($colString, $value);
break;
case DatagridFilter::LESS_EQUAL:
$wheres[] = $where->lessThanOrEqualTo($colString, $value);
break;
case DatagridFilter::LESS:
$wheres[] = $where->lessThan($colString, $value);
break;
case DatagridFilter::BETWEEN:
$wheres[] = $where->between($colString, $values[0], $values[1]);
break;
case DatagridFilter::IN:
$wheres[] = $where->in($colString, (array) $value);
break;
case DatagridFilter::NOT_IN:
$wheres[] = $where->notin($colString, (array) $value);
break;
default:
throw new \InvalidArgumentException('This operator is currently not supported: ' . $filter->getOperator());
break;
}
}
if (count($wheres) > 0) {
$set = new PredicateSet($wheres, PredicateSet::OP_OR);
$select->where->andPredicate($set);
}
}
}