/** * @param $sql * @param \RBM\SqlQuery\Select $select * @return mixed|string * @throws \RBM\SqlQuery\RendererException */ protected function _applyLimit($sql, Select $select) { // dblog('LIMIT - '.$sql); $count = intval($select->getLimitCount()); if ($count <= 0) { throw new RendererException("LIMIT argument count={$count} is not valid"); } $offset = intval($select->getLimitStart()); if ($offset < 0) { throw new RendererException("LIMIT argument offset={$offset} is not valid"); } if ($offset == 0) { $sql = preg_replace('/^SELECT\\s/i', 'SELECT TOP ' . $count . ' ', $sql); } else { $orderby = stristr($sql, 'ORDER BY'); if (!$orderby) { $over = 'ORDER BY (SELECT 0)'; } else { //$over = preg_replace('/\"[^,]*\".\"([^,]*)\"/is', '"inner_tbl"."$1"', $orderby); $substr = substr($orderby, 9); $orders = explode(',', $substr); array_walk($orders, function (&$ord) { $direction = substr($ord, strrpos($ord, ' ') + 1); $col = substr($ord, 0, strrpos($ord, ' ')); $ord = 'inner_tbl.' . substr($col, strrpos($col, '.') + 1) . ' ' . $direction; }); $over = 'ORDER BY ' . implode(',', $orders); } // Remove ORDER BY clause from $sql $sql = preg_replace('/\\s+ORDER BY(.*)/s', '', $sql); // Add ORDER BY clause as an argument for ROW_NUMBER() $sql = "SELECT ROW_NUMBER() OVER ({$over}) AS __rownum__, * FROM ({$sql}) AS inner_tbl"; $start = $offset + 1; $end = $offset + $count; $sql = "WITH outer_tbl AS ({$sql}) SELECT * FROM outer_tbl WHERE CAST(__rownum__ AS int) BETWEEN {$start} AND {$end}"; } return $sql; }
/** * @param Select $select * @return string */ protected function _renderSelectLimit(Select $select) { $mask = is_null($select->getLimitStart()) ? '0' : '1'; $mask .= is_null($select->getLimitCount()) ? '0' : '1'; $separator = ' '; switch ($mask) { case '10': return "LIMIT{$separator}{$select->getLimitStart()}"; case '11': return "LIMIT{$separator}{$select->getLimitStart()}, {$select->getLimitCount()}"; case '01': return "LIMIT{$separator}0, {$select->getLimitCount()}"; } return ''; }