/** * Updates the configured query object with the where-clause. * * @param string $prependQuery Prepends this string to the where-clause * (" WHERE " or " AND " for example) * * @return self */ public function updateQuery($prependQuery = ' WHERE ') { $whereCase = $this->getWhereClause($prependQuery); if ($whereCase !== '') { $this->query->setSQL($this->query->getSQL() . $whereCase); } return $this; }
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; }
/** * Build a filter Native query. * * @param \Symfony\Component\Form\Form $form * @param Doctrine\ORM\NativeQuery $query * @return Doctrine\ORM\NativeQuery */ public function buildNativeQuery(Form $form, NativeQuery $query) { $this->query = $query; //$this->getPlatfornName(); $group_child = $this->groupChild($form); $sql = $query->getSQL(); foreach ($group_child as $field => $child) { if ($condition = $this->applyFilter($child, $field)) { $sql .= ' AND ' . $condition; } } return $query->setSQL($sql)->setParameters($this->parameters); }
public function getSQL() { return $this->nativeQuery->getSQL(); }
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; }