/**
* Set the ORDER BY value
*
* @param mixed $by
* @param string $order
* @return AbstractSql
*/
public function orderBy($by, $order = 'ASC')
{
$byColumns = null;
if (is_array($by)) {
$quotedAry = [];
foreach ($by as $value) {
$quotedAry[] = $this->sql->quoteId(trim($value));
}
$byColumns = implode(', ', $quotedAry);
} else {
if (strpos($by, ',') !== false) {
$ary = explode(',', $by);
$quotedAry = [];
foreach ($ary as $value) {
$quotedAry[] = $this->sql->quoteId(trim($value));
}
$byColumns = implode(', ', $quotedAry);
} else {
$byColumns = $this->sql->quoteId(trim($by));
}
}
$this->orderBy .= (null !== $this->orderBy ? ', ' : '') . $byColumns;
$order = strtoupper($order);
if (strpos($order, 'RAND') !== false) {
$this->orderBy = $this->sql->getDbType() == \Pop\Db\Sql::SQLITE ? ' RANDOM()' : ' RAND()';
} else {
if ($order == 'ASC' || $order == 'DESC') {
$this->orderBy .= ' ' . $order;
}
}
return $this;
}
/**
* Predicate render method
*
* @param int $count
* @return string
*/
public function render($count = 1)
{
$where = null;
// Build any nested predicates
//if (null !== $this->nested) {
// $where = '(' . $this->nested . ')';
//}
if (count($this->nested) > 0) {
$where = '(' . implode(') AND (', $this->nested) . ')';
}
// Loop through and format the predicates
if (count($this->predicates) > 0) {
if (null !== $where) {
$where .= ' ' . $this->predicates[0]['combine'] . ' ';
}
$paramCount = $count;
$dbType = $this->sql->getDbType();
foreach ($this->predicates as $key => $predicate) {
$format = $predicate['format'];
$curWhere = '(';
for ($i = 0; $i < count($predicate['values']); $i++) {
if ($i == 0) {
$format = str_replace('%1', $this->sql->quoteId($predicate['values'][$i]), $format);
} else {
if (is_array($predicate['values'][$i])) {
$vals = $predicate['values'][$i];
foreach ($vals as $k => $v) {
$predValue = strpos($predicate['values'][0], '.') !== false ? substr($predicate['values'][0], strpos($predicate['values'][0], '.') + 1) : $predicate['values'][0];
// Check for named parameters
if (':' . $predValue == substr($v, 0, strlen(':' . $predValue)) && $dbType !== \Pop\Db\Sql::SQLITE && $dbType !== \Pop\Db\Sql::ORACLE) {
if ($dbType == \Pop\Db\Sql::MYSQL || $dbType == \Pop\Db\Sql::SQLSRV) {
$v = '?';
} else {
if ($dbType == \Pop\Db\Sql::PGSQL && !$this->sql->getDb()->isPdo()) {
$v = '$' . $paramCount;
$paramCount++;
}
}
}
$vals[$k] = null === $v ? 'NULL' : $this->sql->quote($v);
}
$format = str_replace('%' . ($i + 1), implode(', ', $vals), $format);
} else {
if ($predicate['values'][$i] instanceof \Pop\Db\Sql) {
$val = (string) $predicate['values'][$i];
} else {
$val = null === $predicate['values'][$i] ? 'NULL' : $this->sql->quote($predicate['values'][$i]);
}
$predValue = strpos($predicate['values'][0], '.') !== false ? substr($predicate['values'][0], strpos($predicate['values'][0], '.') + 1) : $predicate['values'][0];
// Check for named parameters
if (':' . $predValue == substr($val, 0, strlen(':' . $predValue)) && $dbType !== \Pop\Db\Sql::SQLITE && $dbType !== \Pop\Db\Sql::ORACLE) {
if ($dbType == \Pop\Db\Sql::MYSQL || $dbType == \Pop\Db\Sql::SQLSRV) {
$val = '?';
} else {
if ($dbType == \Pop\Db\Sql::PGSQL && !$this->sql->getDb()->isPdo()) {
$val = '$' . $paramCount;
$paramCount++;
}
}
}
$format = str_replace('%' . ($i + 1), $val, $format);
}
}
}
$curWhere .= $format . ')';
if ($key == 0) {
$where .= $curWhere;
} else {
$where .= ' ' . $predicate['combine'] . ' ' . $curWhere;
}
}
}
return $where;
}
public function testQuoteId()
{
$s = new Sql(Db::factory('Sqlite', array('database' => __DIR__ . '/../tmp/test.sqlite')), 'users');
$this->assertEquals('"users"', $s->quoteId('users'));
$this->assertEquals('"users"."id"', $s->quoteId('users.id'));
$s->setQuoteId(Sql::BACKTICK);
$this->assertEquals('`users`', $s->quoteId('users'));
$this->assertEquals('`users`.`id`', $s->quoteId('users.id'));
$s->setQuoteId(Sql::BRACKET);
$this->assertEquals('[users]', $s->quoteId('users'));
$this->assertEquals('[users].[id]', $s->quoteId('users.id'));
}
