private function _fetchPaginationNativeQuery($params, \Doctrine\ORM\NativeQuery $nQuery) { $strQuery = $nQuery->getSQL(); # encapsula a consulta enviada numa subconsulta # para permitir a paginacao e limitacao do resultado # sem necessitar alterar a query enviada $improvedQuery = sprintf('SELECT * FROM (%s) AS foo ', $strQuery); $improvedQueryCount = sprintf('SELECT count(1) total FROM (%s) C', $improvedQuery); # se necesario, aplica ordenacao if (isset($params['order'])) { foreach ($params['order'] as $order) { $orderBy[] = sprintf('%s %s', $order['sort'], $order['order']); } if (!empty($orderBy)) { $improvedQuery .= sprintf(' %s %s', self::T_ORDER_BY, implode(', ', $orderBy)); } } /** * Cria uma query para recupera o total geral de registros */ $rsmCount = new \Doctrine\ORM\Query\ResultSetMapping($this->_em); $rsmCount->addScalarResult('total', 'total', 'integer'); $nativeQueryCount = $this->_em->createNativeQuery($improvedQueryCount, $rsmCount); foreach ($nQuery->getParameters() as $key => $value) { $nativeQueryCount->setParameter(":{$key}", $value); } $improvedQuery .= sprintf(' %1$s %4$d %2$s %3$d', self::T_LIMIT, self::T_OFFSET, $params['iDisplayStart'], $params['iDisplayLength']); $nQuery->setSQL($improvedQuery); $result['data'] = $nQuery->getResult(); $result['total'] = $nativeQueryCount->getSingleScalarResult(); return $result; }
public function getParameters() { return $this->nativeQuery->getParameters(); }
private function _fetchPaginationNativeQuery($params, \Doctrine\ORM\NativeQuery $nQuery, $withoutNativeCount = FALSE) { $strQuery = $nQuery->getSQL(); //caso a query já possua um limit e offset if (strpos(strtolower($strQuery), ':limit') !== FALSE) { $paramLimit = $nQuery->getParameter('limit'); if (!$paramLimit) { $nQuery->setParameter('limit', $params['iDisplayLength']); } if (strpos(strtolower($strQuery), ':offset') !== FALSE) { $paramOffset = $nQuery->getParameter('offset'); if (!$paramOffset) { $nQuery->setParameter('offset', $params['iDisplayStart']); } } $improvedQuery = $strQuery; } else { # encapsula a consulta enviada numa subconsulta # para permitir a paginacao e limitacao do resultado # sem necessitar alterar a query enviada $improvedQuery = sprintf('SELECT * FROM (%s) AS foo ', $strQuery); $improvedQueryCount = sprintf('SELECT count(1) total FROM (%s) C', $improvedQuery); # se necesario, aplica ordenacao if (isset($params['order'])) { foreach ($params['order'] as $order) { $orderBy[] = sprintf('%s %s', $order['sort'], $order['order']); } if (!empty($orderBy)) { $improvedQuery .= sprintf(' %s %s', self::T_ORDER_BY, implode(', ', $orderBy)); } } $improvedQuery .= sprintf(' %1$s %4$d %2$s %3$d', self::T_LIMIT, self::T_OFFSET, $params['iDisplayStart'], $params['iDisplayLength']); } if (!$withoutNativeCount) { /** * Cria uma query para recupera o total geral de registros */ $rsmCount = new \Doctrine\ORM\Query\ResultSetMapping($this->_em); $rsmCount->addScalarResult('total', 'total', 'integer'); $nativeQueryCount = $this->_em->createNativeQuery($improvedQueryCount, $rsmCount); foreach ($nQuery->getParameters() as $key => $value) { $nativeQueryCount->setParameter(":{$key}", $value); } } $nQuery->setSQL($improvedQuery); $result['data'] = $nQuery->getResult(); if (!$withoutNativeCount) { $total = $nativeQueryCount->getSingleScalarResult(); } else { $total = 0; if ($result['data']) { //recupera o total_record da primeria posição do resultado if (!isset($result['data'][0]['totalRecord'])) { trigger_error('Não foi setada coluna "total_record no nativeQuery"', E_USER_ERROR); } $total = $result['data'][0]['totalRecord']; } } $result['total'] = $total; return $result; }