/**
* @param QueryBuilder $queryBuilder
* @param ResourceFilter $filter
* @param ForestCollection $collection
* @param string $alias
*/
public function filterQueryBuilder($queryBuilder, $filter, $collection, $alias)
{
if ($filter->hasSearch()) {
$nested = $queryBuilder->expr()->orX();
$searchValue = $queryBuilder->expr()->literal($filter->getSearch());
foreach ($collection->getFields() as $field) {
$fieldName = $alias . '.' . $field->getField();
if ($fieldName == $alias . '.' . $collection->getIdentifier() || $field->getType() == 'String') {
$nested->add($queryBuilder->expr()->eq($fieldName, $searchValue));
}
}
$queryBuilder->andWhere($nested);
}
if ($filter->hasFilters()) {
foreach ($filter->getFilters() as $f) {
/** @var DataFilter $f */
$fieldName = $alias . '.' . $f->getFieldName();
$filterValue = $queryBuilder->expr()->literal($f->getFilterString());
if ($f->isDifferent()) {
$queryBuilder->andWhere($queryBuilder->expr()->neq($fieldName, $filterValue));
} elseif ($f->isGreaterThan()) {
$queryBuilder->andWhere($queryBuilder->expr()->gt($fieldName, $filterValue));
} elseif ($f->isLowerThan()) {
$queryBuilder->andWhere($queryBuilder->expr()->lt($fieldName, $filterValue));
} elseif ($f->isContains() || $f->isStartsBy() || $f->isEndsBy()) {
$filterValue = $f->getFilterString();
if ($f->isContains() || $f->isStartsBy()) {
$filterValue = $filterValue . '%';
}
if ($f->isContains() || $f->isEndsBy()) {
$filterValue = '%' . $filterValue;
}
$filterValue = $queryBuilder->expr()->literal($filterValue);
$queryBuilder->andWhere($queryBuilder->expr()->like($fieldName, $filterValue));
} elseif ($f->isPresent()) {
$queryBuilder->andWhere($queryBuilder->expr()->isNotNull($fieldName));
} elseif ($f->isBlank()) {
$nested = $queryBuilder->expr()->orX($queryBuilder->expr()->isNull($fieldName), $queryBuilder->expr()->eq($fieldName, $queryBuilder->expr()->literal('')));
$queryBuilder->andWhere($nested);
} else {
$queryBuilder->andWhere($queryBuilder->expr()->eq($fieldName, $filterValue));
}
}
}
if ($filter->hasSortBy()) {
$queryBuilder->addOrderBy($alias . '.' . $filter->getSortBy(), $filter->getSortOrder());
}
}
public function filterQueryBuilder(ResourceFilter $filter, $collection, $queryBuilder = null, $associatedTableName = null)
{
// TODO: Maybe find a better way to get the table name
$tableName = App::make($collection->getEntityClassName())->getTable();
if (is_null($queryBuilder)) {
$queryBuilder = DB::table($tableName);
}
if ($filter->hasSearch()) {
$queryBuilder->orWhere(function ($query) use($filter, $collection) {
foreach ($collection->getFields() as $field) {
if ($field->getField() == $collection->getIdentifier() || $field->getType() == 'String') {
// Test for a search with a like rather than a basic equal
// $query->orWhere($field->getField(), '=', $filter->getSearch());
$query->orWhere($field->getField(), 'like', '%' . $filter->getSearch() . '%');
}
}
});
}
if ($filter->hasFilters()) {
foreach ($filter->getFilters() as $newfilter) {
$fieldName = $newfilter->getFieldName();
$filterValue = $newfilter->getFilterString();
if ($newfilter->isDifferent()) {
$queryBuilder->where($fieldName, '!=', $filterValue);
} elseif ($newfilter->isGreaterThan()) {
$queryBuilder->where($fieldName, '<', $filterValue);
} elseif ($newfilter->isLowerThan()) {
$queryBuilder->where($fieldName, '>', $filterValue);
} elseif ($newfilter->isContains() || $newfilter->isStartsBy() || $newfilter->isEndsBy()) {
if ($newfilter->isContains() || $newfilter->isStartsBy()) {
$filterValue = $filterValue . '%';
}
if ($newfilter->isContains() || $newfilter->isEndsBy()) {
$filterValue = '%' . $filterValue;
}
$queryBuilder->where($fieldName, 'like', $filterValue);
} elseif ($newfilter->isPresent()) {
$queryBuilder->whereNotNull($fieldName);
} elseif ($newfilter->isBlank()) {
$queryBuilder->where(function ($query) use($fieldName) {
$query->whereNull($fieldName);
$query->orWhere($fieldName, '=', '');
});
} else {
$queryBuilder->where($fieldName, '=', $filterValue);
}
}
}
if ($filter->hasSortBy()) {
if (is_null($associatedTableName)) {
$queryBuilder->orderBy($filter->getSortBy(), $filter->getSortOrder());
} else {
$queryBuilder->orderBy($associatedTableName . '.' . $filter->getSortBy(), $filter->getSortOrder());
}
}
return $queryBuilder;
}
public function testSearch()
{
$rf = new ResourceFilter(array('search' => 3));
$this->assertTrue($rf->hasSearch());
$this->assertEquals(3, $rf->getSearch());
}
