/**
* @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;
}
<?php
require '../vendor/autoload.php';
use RBM\SqlQuery\Select;
use RBM\SqlQuery\Filter;
use RBM\SqlQuery\Renderer\MySql;
Select::setDefaultRenderer(new MySql());
function printQuery($query)
{
echo $query;
echo PHP_EOL . PHP_EOL . "===========================" . PHP_EOL;
}
/**
* @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 '';
}
public function testOrderBy()
{
$select = new Select('project');
$select->orderBy("date_created");
$ob = $select->getOrderBy();
$this->assertCount(1, $ob);
$this->assertInstanceOf('\\RBM\\SqlQuery\\OrderBy', $ob[0]);
$select->join('user', 'owner_id', 'user_id')->orderBy('name');
$ob = $select->getAllOrderBy();
$this->assertCount(2, $ob);
}
function printQuery($query)
{
echo PHP_EOL . PHP_EOL;
echo Select::getDefaultRenderer()->format($query);
echo PHP_EOL . PHP_EOL . "===========================" . PHP_EOL;
}
