/**
* @param Query|QueryBuilder $criteria
*
* @return string (sql)
*/
protected function getSqlCriteria($criteria)
{
if ($criteria instanceof QueryBuilder) {
return $criteria->getQuery()->getSQL();
} elseif ($criteria instanceof Query) {
return $criteria->getSQL();
} else {
throw new \Exception(sprintf('Criteria must be instance of Query or QueryBuilder, instance of %s given', get_class($criteria)));
}
}
/**
* Get Paginator
*
* @param integer $limit ( limit per page )
* @param array $options
* @return \Knp\Bundle\PaginatorBundle\Pagination\SlidingPagination
*/
protected function getPaginator($limit = 30, $options = array())
{
$page = $this->get('request')->query->get('page', 1);
$cnt = 0;
if ($this->get('request')->query->has('page')) {
$page = $this->get('request')->query->get('page');
$this->get('session')->set('_pager_' . $this->get('request')->get('_route'), $page);
} elseif ($this->get('session')->has('_pager_' . $this->get('request')->get('_route'))) {
$page = $this->get('session')->get('_pager_' . $this->get('request')->get('_route'));
}
if (isset($options['total_item_count']) and (int) $options['total_item_count']) {
$cnt = $options['total_item_count'];
}
$this->paginator = $this->get('knp_paginator');
if (is_array($this->query) or $this->query instanceof QueryBuilder) {
$pagination = $this->paginator->paginate($this->query, $page, $limit, $options);
} elseif ($this->query instanceof NativeQuery) {
$rsm = new ResultSetMapping();
$rsm->addScalarResult('cnt', 'cnt');
$q = strstr($this->query->getSQL(), " FROM ");
$q = "SELECT COUNT(*) cnt " . $q;
$cntQuery = $this->em->createNativeQuery($q, $rsm)->setParameters($this->query->getParameters());
try {
$cnt = $cntQuery->getSingleScalarResult();
} catch (\Doctrine\Orm\NoResultException $e) {
$cnt = 0;
}
$sql = $this->query->getSQL();
$pagination = $this->paginator->paginate(array());
$sort_name = $pagination->getPaginatorOption('sortFieldParameterName');
$sort_direction_name = $pagination->getPaginatorOption('sortDirectionParameterName');
if ($this->get('request')->query->has($sort_name) and $this->get('request')->query->has($sort_direction_name)) {
$sql .= ' ORDER BY ' . $this->get('request')->query->get($sort_name) . ' ' . $this->get('request')->query->get($sort_direction_name);
} elseif (isset($options['default_sort']) and $options['default_sort']) {
$sql .= ' ORDER BY ';
foreach ($options['default_sort'] as $field => $type) {
$sql .= $field . ' ' . $type . ',';
}
$sql = trim($sql, ',');
}
if (!isset($options['not_use_limit_offset'])) {
$offset = $limit * ($page - 1);
$this->query->setSQL($sql . ' LIMIT ' . $limit . ' OFFSET ' . $offset);
}
$pagination->setCurrentPageNumber($page);
$pagination->setItemNumberPerPage($limit);
$pagination->setTotalItemCount($cnt);
$pagination->setItems($this->query->getResult());
}
$pagination->setTemplate($this->container->getParameter('zk2_admin_panel.pagination_template'));
$pagination->setSortableTemplate($this->container->getParameter('zk2_admin_panel.sortable_template'));
return compact('pagination');
}
