private function doTestGetSlice(DoctrineDbalAdapter $adapter)
{
$offset = 30;
$length = 10;
$qb = clone $this->qb;
$qb->setFirstResult($offset)->setMaxResults($length);
$expectedResults = $qb->execute()->fetchAll();
$results = $adapter->getSlice($offset, $length);
$this->assertSame($expectedResults, $results);
}
public function getListAction($objectType, Request $request)
{
$queryBuilder = $this->dbal->createQueryBuilder()->select('o.*')->from($objectType, 'o');
if ($sort = $request->query->get('_sort')) {
$queryBuilder->orderBy($sort, $request->query->get('_sortDir', 'ASC'));
}
$countQueryBuilderModifier = function ($queryBuilder) {
$queryBuilder->select('COUNT(DISTINCT o.id) AS total_results')->setMaxResults(1);
};
$pager = new DoctrineDbalAdapter($queryBuilder, $countQueryBuilderModifier);
$nbResults = $pager->getNbResults();
$results = $pager->getSlice($request->query->get('_start', 0), $request->query->get('_end', 20));
return new JsonResponse($results, 200, array('X-Total-Count' => $nbResults));
}
/**
* Constructor.
*
* @param QueryBuilder $queryBuilder A DBAL query builder.
* @param string $countField Primary key for the table in query. Used in count expression. Must include table alias
*/
public function __construct(QueryBuilder $queryBuilder, $countField)
{
if ($this->hasQueryBuilderJoins($queryBuilder)) {
throw new InvalidArgumentException('The query builder cannot have joins.');
}
$countQueryBuilderModifier = $this->createCountQueryModifier($countField);
parent::__construct($queryBuilder, $countQueryBuilderModifier);
}
public function getListAction($objectType, Request $request)
{
// Prepare-prefixes
$strongFilterKeyPrefix = 'strong_filter_';
$searchOrKeyPrefix = 'search_or_';
$searchAndKeyPrefix = 'search_and_';
$queryBuilder = $this->dbal->createQueryBuilder()->from($objectType, 'o');
$filterNames = array('_strongFilter', '_strongFilterIn', '_searchOr', '_searchAnd');
// Fetching filters from request
$filters = array_combine($filterNames, array_map(function ($filter) use($request) {
return $request->query->get($filter);
}, $filterNames));
// Throw expression when count of filters in request greater then one
$count = array_reduce($filters, function ($carry, $item) {
return $carry + ($item !== null);
}, 0);
if ($count > 1) {
return new JsonResponse(array('status' => 'ERROR', 'status_code' => 400, 'message' => 'You should use only one type of filters per request'), 400);
}
// Return only assigned fields
$fields = $request->query->get('_fields');
$queryBuilder->select($fields ? preg_replace('/([^,]+)/', 'o.$1', $fields) : 'o.*');
// Strong filter implementing
// o.f1 = 'val1' AND o.f2 = 'val2' ...
if ($filters['_strongFilter']) {
$queryBuilder->where(implode(' and ', array_map(function ($item) use($strongFilterKeyPrefix) {
return "{$item} = :{$strongFilterKeyPrefix}{$item}";
}, array_keys($filters['_strongFilter']))))->setParameters(array_combine(array_map(function ($key) use($strongFilterKeyPrefix) {
return $strongFilterKeyPrefix . $key;
}, array_keys($filters['_strongFilter'])), $filters['_strongFilter']));
}
// Searching with OR:
// o.f1 LIKE '%val1%' OR o.f2 LIKE '%val2%'
if ($filters['_searchOr']) {
foreach ($filters['_searchOr'] as $key => $value) {
$queryBuilder->orWhere($queryBuilder->expr()->like($key, ":" . $searchOrKeyPrefix . $key))->setParameter($searchOrKeyPrefix . $key, "%{$value}%");
}
}
if ($filters['_strongFilterIn']) {
foreach ($filters['_strongFilterIn'] as $key => $value) {
$queryBuilder->andWhere($queryBuilder->expr()->in($key, explode(',', $value)));
}
}
// Searching with AND:
// o.f1 LIKE '%val1%' AND o.f2 LIKE '%val2%'
if ($filters['_searchAnd']) {
foreach ($filters['_searchAnd'] as $key => $value) {
$queryBuilder->andWhere($queryBuilder->expr()->like($key, ":" . $searchAndKeyPrefix . $key))->setParameter($searchAndKeyPrefix . $key, "%{$value}%");
}
}
if ($group = $request->query->get('_group')) {
$queryBuilder->groupBy($group);
}
if ($sort = $request->query->get('_sort')) {
$queryBuilder->orderBy($sort, $request->query->get('_sortDir', 'ASC'));
}
$countQueryBuilderModifier = function ($queryBuilder) {
$queryBuilder->select('COUNT(DISTINCT o.id) AS total_results')->setMaxResults(1);
};
$pager = new DoctrineDbalAdapter($queryBuilder, $countQueryBuilderModifier);
$nbResults = $pager->getNbResults();
$results = $pager->getSlice($request->query->get('_start', 0), $request->query->get('_end', 20));
return new JsonResponse($results, 200, array('X-Total-Count' => $nbResults));
}