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; }