public function testSort() { $rf = new ResourceFilter(array('sort' => 'plok')); $this->assertTrue($rf->hasSortBy()); $this->assertEquals('ASC', $rf->getSortOrder()); $this->assertEquals('plok', $rf->getSortBy()); $rf = new ResourceFilter(array('sort' => '-foobar')); $this->assertTrue($rf->hasSortBy()); $this->assertEquals('DESC', $rf->getSortOrder()); $this->assertEquals('foobar', $rf->getSortBy()); $rf = new ResourceFilter(array('sort' => '-')); $this->assertFalse($rf->hasSortBy()); $this->assertEquals('ASC', $rf->getSortOrder()); $this->assertNull($rf->getSortBy()); }
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; }
/** * @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()); } }