/** * 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; }
/** * @return NativeQueryWrapper */ public function getQuery() { $query = new Doctrine\ORM\NativeQuery($this->em); $query->setResultSetMapping($this->getResultSetMapper()); $query->setParameters($this->getParameters()); $wrapped = new NativeQueryWrapper($query); $wrapped->setFirstResult($this->getFirstResult()); $wrapped->setMaxResults($this->getMaxResults()); $hasSelect = (bool) $this->getQueryPart('select'); if (!$hasSelect && $this->getType() === self::SELECT) { $select = $this->getResultSetMapper()->generateSelectClause(); $this->select($select ?: '*'); } $query->setSQL($this->getSQL()); $this->setFirstResult($wrapped->getFirstResult()); $this->setMaxResults($wrapped->getMaxResults()); if (!$hasSelect && $this->getType() === self::SELECT) { $this->resetQueryPart('select'); } $rsm = $this->getResultSetMapper(); if (empty($rsm->fieldMappings) && empty($rsm->scalarMappings)) { throw new InvalidStateException("No field or columns mapping found, please configure the ResultSetMapper and some fields."); } return $wrapped; }
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 setSQL($sql) { $this->nativeQuery->setSQL($sql); return $this; }
/** * {@inheritDoc} */ public function createNativeQuery($sql, ResultSetMapping $rsm) { $query = new NativeQuery($this); $query->setSQL($sql); $query->setResultSetMapping($rsm); return $query; }
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; }