protected function setParams(NativeQuery $query) { foreach ($this->bindParams as $key => $val) { $query->setParameter("{$key}", $val); } }
public function setParameter($key, $value, $type = null) { $this->nativeQuery->setParameter($key, $value, $type); return $this; }
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; }