public function getCategories(array $options = [])
{
$currentPage = isset($options['page']) ? $options['page'] : 0;
$pageSize = isset($options['pageSize']) ? $options['pageSize'] : 10;
$query = $this->qb->select('cat')->from('Api\\Model\\Categories', 'cat')->orderBy('cat.catId', 'DESC');
$paginator = new \Doctrine\ORM\Tools\Pagination\Paginator($query);
$totalItems = $paginator->count();
$paginator->getQuery()->setFirstResult($pageSize * $currentPage)->setMaxResults($pageSize)->getArrayResult();
$list = [];
foreach ($paginator as $item) {
$list[] = $item;
}
return ['data' => $list, 'totalRecords' => $totalItems];
}
public function setupDatatable($columnConfig = null, $filter = null)
{
$columns = $columnConfig;
// Add an empty field to the beginning of columns (e.g. used for checkbox)
array_unshift($columns, ['field' => '']);
/* @var $qb \Doctrine\DBAL\Query\QueryBuilder */
$qb = $this->getEntityManager()->createQueryBuilder();
$qb->from($this->getEntityName(), static::ALIAS);
// Initialize select clause
foreach ($columns as $num => $c) {
$field = $c['field'];
if (!empty($field)) {
// When requesting an simple attribute, e.g. an integer or string value like id and description
if (!array_key_exists('joinEntity', $c)) {
// Add select
$qb->addSelect(static::ALIASDOT . $field);
} else {
// When requesting an entity, e.g. status, customer
$entity = $c['joinEntity'];
$join = static::ALIASDOT . $entity;
$joinResultField = $c['joinResultField'];
$jAlias = $entity . 'Alias';
// Add select
$qb->addSelect("{$jAlias}.{$joinResultField} as {$entity}_{$joinResultField}_{$num}");
// Check whether join exists already
$joinDqlParts = $qb->getDQLParts()['join'];
$aliasAlreadyExists = false;
/* @var $j \Doctrine\ORM\Query\Expr\Join */
foreach ($joinDqlParts as $joins) {
foreach ($joins as $j) {
if ($j->getAlias() === $jAlias) {
$aliasAlreadyExists = true;
break 2;
}
}
}
if ($aliasAlreadyExists === false) {
// Add join
$qb->leftJoin($join, $jAlias);
}
}
}
}
// Group filter
if (isset($_POST['customActionName']) && $_POST['customActionName'] == 'filterByStatus') {
$value = $_POST['customActionValue'];
if ($value) {
$qb->andWhere("statusAlias.id = {$value}");
}
}
// Program filter
if ($filter) {
foreach ($filter as $f) {
if (isset($f['method']) && $f['method'] == 'or') {
$qb->orWhere($f['query']);
} else {
$qb->andWhere($f);
}
}
}
// Datatable Filter
if (isset($_POST['filter'])) {
$this->_applyFilterCriteriaStrategy($_POST['filter'], $columns, $qb);
}
if (isset($_POST['order'])) {
$col = $_POST['order'][0]['column'];
$sort = static::ALIASDOT . $columns[$col]['field'];
if (array_key_exists('joinEntity', $columns[$col])) {
$sort = $columns[$col]['joinEntity'] . 'Alias' . static::CONNECTOR . $columns[$col]['joinResultField'];
}
switch ($_POST['order'][0]['dir']) {
case 'asc':
$qb->orderBy($sort, 'asc');
break;
case 'desc':
$qb->orderBy($sort, 'desc');
break;
}
}
// Pagination, returns a page of results each time based on above.
$paginator = new \Doctrine\ORM\Tools\Pagination\Paginator($qb, $fetchJoinCollection = false);
$paginator->setUseOutputWalkers(false);
// display limited records
if (array_key_exists('length', $_POST) && $_POST['length'] > 0) {
$paginator->getQuery()->setFirstResult($_POST['start'])->setMaxResults($_POST['length']);
}
//$start = new \DateTime( date('Y-m-d H:i:s.'.sprintf("%06d",(microtime(true) - floor(microtime(true))) * 1000000), microtime(true)) ); echo '1st: ' . $start->format('d-m-Y h:i:s.u') . "<br>";
// Write own query to count total for efficiency
$query = $this->getEntityManager()->createQuery('SELECT COUNT(q.id) FROM ' . $this->getEntityName() . ' q');
$iTotal = $query->getSingleScalarResult();
//$mid = new \DateTime( date('Y-m-d H:i:s.'.sprintf("%06d",(microtime(true) - floor(microtime(true))) * 1000000), microtime(true)) ); echo '2nd: ' . $mid->format('d-m-Y h:i:s.u') . "<br>";
if (empty($qb->getDQLParts()['where'])) {
$iFilteredTotal = $iTotal;
} else {
$iFilteredTotal = $paginator->count();
}
$result = array();
foreach ($paginator as $order) {
$keys = array_keys($order);
$row = array();
for ($i = 0; $i < count($columns); $i++) {
if (empty($columns[$i]['field'])) {
$row[] = '';
} else {
$value = $this->_applyWordingStrategy($value = $order[$keys[$i - 1]], $strategyContainer = $columns[$i]);
$row[] = $value;
}
$row['DT_RowId'] = $order['id'];
}
$result[] = $row;
}
$output = array("data" => $result, "draw" => intval($_POST['draw']), "recordsTotal" => $iTotal, "recordsFiltered" => $iFilteredTotal);
//$end = new \DateTime( date('Y-m-d H:i:s.'.sprintf("%06d",(microtime(true) - floor(microtime(true))) * 1000000), microtime(true)) ); echo '3rd: ' . $end->format('d-m-Y h:i:s.u');
return $output;
}
