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