public function testBetween()
{
$filter = new Filter();
$filter->setFromColumn($this->column, '2<>3');
$this->assertEquals(Filter::BETWEEN, $filter->getOperator());
$this->assertEquals('2 <> 3', $filter->getDisplayColumnValue());
$this->assertEquals(['2', '3'], $filter->getValues());
$filter = new Filter();
$filter->setFromColumn($this->column, '2<>3 <>4');
$this->assertEquals(Filter::BETWEEN, $filter->getOperator());
$this->assertEquals('2 <> 4', $filter->getDisplayColumnValue());
$this->assertEquals(['2', '4'], $filter->getValues());
}
/**
* @param DatagridFilter $filter
* @throws \Exception
*/
public function applyFilter(DatagridFilter $filter)
{
$select = $this->getSelect();
$adapter = $this->getSql()->getAdapter();
$qi = function ($name) use($adapter) {
return $adapter->getPlatform()->quoteIdentifier($name);
};
$col = $filter->getColumn();
if (!$col instanceof Column\Select) {
throw new \Exception('This column cannot be filtered: ' . $col->getUniqueId());
}
$colString = $col->getSelectPart1();
if ($col->getSelectPart2() != '') {
$colString .= '.' . $col->getSelectPart2();
}
if ($col instanceof Column\Select && $col->hasFilterSelectExpression()) {
$colString = sprintf($col->getFilterSelectExpression(), $colString);
}
$values = $filter->getValues();
$wheres = [];
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 ?', ['%' . $value . '%']);
break;
case DatagridFilter::NOT_LIKE_LEFT:
$wheres[] = $where->literal($qi($colString) . 'NOT LIKE ?', ['%' . $value]);
break;
case DatagridFilter::NOT_LIKE_RIGHT:
$wheres[] = $where->literal($qi($colString) . 'NOT LIKE ?', [$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 2;
default:
throw new \InvalidArgumentException('This operator is currently not supported: ' . $filter->getOperator());
break;
}
}
if (!empty($wheres)) {
$set = new PredicateSet($wheres, PredicateSet::OP_OR);
$select->where->andPredicate($set);
}
}
public function testBetween()
{
$filter = new \ZfcDatagrid\Filter();
$filter->setFromColumn($this->column, '15 <> 30');
$this->assertEquals(Filter::BETWEEN, $filter->getOperator());
$filterArray = new FilterArray($filter);
$this->assertTrue($filterArray->applyFilter(['myCol' => '15']));
$this->assertTrue($filterArray->applyFilter(['myCol' => '20']));
$this->assertTrue($filterArray->applyFilter(['myCol' => '30']));
$this->assertFalse($filterArray->applyFilter(['myCol' => '14']));
$this->assertFalse($filterArray->applyFilter(['myCol' => '31']));
}
/**
* @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);
}
}
