/** * @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'); }