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