/**
* getList
*
* @param Query $query
* @param integer $start
* @param integer $limit
*
* @return \stdClass[]
*/
public function getList(Query $query, $start = null, $limit = null)
{
$query->limit($start, $limit);
if (WINDWALKER_DEBUG) {
$profiler = Ioc::getProfiler();
$profiler->mark(uniqid() . ' - ' . (string) $query->dump());
}
$select = $query->select;
$select = str_replace('SELECT ', 'SQL_CALC_FOUND_ROWS ', $select);
$query->clear('select')->select($select);
return $this->db->getReader($query)->loadObjectList();
}
/**
* getListQuery
*
* @param Query $query
*
* @return Query
*/
protected function getListQuery(Query $query)
{
$queryHelper = $this->getQueryHelper();
$queryHelper->addTable('cat', 'categories')->addTable('blog', 'blogs', 'blog.id = cat.blog');
$query->select($queryHelper->getSelectFields())->where($query->format('%n = %q', 'blog.id', $this['blog.id']));
$query = $queryHelper->registerQueryTables($query);
if ($this['list.search']) {
$search[] = $query->format('%n LIKE %q', 'title', '%' . $this['list.search'] . '%');
$search[] = $query->format('%n LIKE %q', 'alias', '%' . $this['list.search'] . '%');
$query->where(new QueryElement('()', $search, ' OR '));
}
return $query;
}
/**
* getListQuery
*
* @param Query $query
*
* @return Query
*/
protected function getListQuery(Query $query)
{
$queryHelper = $this->getQueryHelper();
$queryHelper->addTable('post', 'posts')->addTable('author', 'authors', 'post.author = author.id')->addTable('user', 'users', 'user.id = author.user')->addTable('blog', 'blogs', 'post.blog = blog.id');
$query->select($queryHelper->getSelectFields())->where($query->format('%n = %q', 'blog.id', $this['blog.id']))->where($query->format('%n = %q', 'type', $this['post.type']));
$query = $queryHelper->registerQueryTables($query);
if ($this['list.search']) {
$search[] = $query->format('%n LIKE %q', 'title', '%' . $this['list.search'] . '%');
$search[] = $query->format('%n LIKE %q', 'alias', '%' . $this['list.search'] . '%');
$query->where(new QueryElement('()', $search, ' OR '));
}
if ($this['list.ordering']) {
$query->order($this['list.ordering']);
}
return $query;
}
/**
* getListQuery
*
* @param Query $query
*
* @return Query
*/
protected function getListQuery(Query $query)
{
$queryHelper = $this->getQueryHelper();
$queryHelper->addTable('author', 'authors')->addTable('user', 'users', 'user.id = author.user')->addTable('blog', 'blogs', 'blog.id = author.blog');
$query->select($queryHelper->getSelectFields());
if ($this['blog.id']) {
$query->where($query->format('%n = %q', 'blog.id', $this['blog.id']));
}
if ($this['find.query']) {
$q = '%' . $this['find.query'] . '%';
$conditions[] = $query->format('%n LIKE %q', 'user.username', $q);
$conditions[] = $query->format('%n LIKE %q', 'user.fullname', $q);
$conditions[] = $query->format('%n LIKE %q', 'user.email', $q);
$conditions[] = $query->format('%n LIKE %q', 'author.name', $q);
$query->where(new QueryElement('()', $conditions, ' OR '));
}
$query = $queryHelper->registerQueryTables($query);
if ($this['list.search']) {
$search[] = $query->format('%n LIKE %q', 'title', '%' . $this['list.search'] . '%');
$search[] = $query->format('%n LIKE %q', 'alias', '%' . $this['list.search'] . '%');
$query->where(new QueryElement('()', $search, ' OR '));
}
return $query;
}
/**
* buildConditions
*
* @param Query $query
* @param array $conditions
*
* @return Query
*/
public static function buildWheres(Query $query, array $conditions)
{
foreach ($conditions as $key => $value) {
if (empty($value)) {
continue;
}
// If using Compare class, we convert it to string.
if ($value instanceof Compare) {
$query->where((string) static::buildCompare($key, $value, $query));
} elseif (is_numeric($key)) {
$query->where((string) $value);
} elseif (is_array($value) || is_object($value)) {
$value = array_map(array($query, 'quote'), (array) $value);
$query->where($query->quoteName($key) . new QueryElement('IN ()', $value, ','));
} else {
$query->where($query->format('%n = %q', $key, $value));
}
}
return $query;
}
/**
* Clear data from the query or a specific clause of the query.
*
* @param string $clause Optionally, the name of the clause to clear, or nothing to clear the whole query.
*
* @return PostgresqlQuery Returns this object to allow chaining.
*
* @since 2.0
*/
public function clear($clause = null)
{
switch ($clause) {
case 'limit':
$this->limit = null;
break;
case 'offset':
$this->offset = null;
break;
case 'forUpdate':
$this->forUpdate = null;
break;
case 'forShare':
$this->forShare = null;
break;
case 'noWait':
$this->noWait = null;
break;
case 'returning':
$this->returning = null;
break;
case 'select':
case 'update':
case 'delete':
case 'insert':
case 'from':
case 'join':
case 'set':
case 'where':
case 'group':
case 'having':
case 'order':
case 'columns':
case 'values':
parent::clear($clause);
break;
default:
$this->type = null;
$this->limit = null;
$this->offset = null;
$this->forUpdate = null;
$this->forShare = null;
$this->noWait = null;
$this->returning = null;
parent::clear($clause);
break;
}
return $this;
}
/**
* Method to append the primary keys for this table to a query.
*
* @param Query $query A query object to append.
* @param mixed $pk Optional primary key parameter.
*
* @return $this Method allows chaining
*
* @since 2.0
*/
public function appendPrimaryKeys(Query $query, $pk = null)
{
if (is_null($pk)) {
foreach ($this->keys as $k) {
$query->where($this->db->quoteName($k) . ' = ' . $this->db->quote($this->{$k}));
}
} else {
if (is_string($pk)) {
$pk = array($this->keys[0] => $pk);
}
$pk = (object) $pk;
foreach ($this->keys as $k) {
$query->where($this->db->quoteName($k) . ' = ' . $this->db->quote($pk->{$k}));
}
}
return $this;
}
/**
* Magic function to convert the query to a string.
*
* @return string The completed query.
*
* @since 2.0
*/
public function __toString()
{
$query = '';
switch ($this->type) {
case 'insert':
$query .= (string) $this->insert;
// Set method
if ($this->set) {
$query .= (string) $this->set;
} elseif ($this->values) {
if ($this->columns) {
$query .= (string) $this->columns;
}
$elements = $this->insert->getElements();
$tableName = array_shift($elements);
$query .= ' VALUES ';
$query .= (string) $this->values;
if ($this->autoIncrementField) {
$query = 'SET IDENTITY_INSERT ' . $tableName . ' ON;' . $query . 'SET IDENTITY_INSERT ' . $tableName . ' OFF;';
}
if ($this->where) {
$query .= (string) $this->where;
}
}
break;
default:
$query = parent::__toString();
break;
}
return $query;
}
/**
* buildConditions
*
* @param Query $query
* @param array $conditions
*
* @return Query
*/
public static function buildWheres(Query $query, array $conditions)
{
foreach ($conditions as $key => $value) {
// NULL
if ($value === null) {
$query->where($query->format('%n = NULL', $key));
} elseif ($value instanceof Compare) {
$query->where((string) static::buildCompare($key, $value, $query));
} elseif (is_numeric($key)) {
$query->where($query->format('%n = %a', $key, $value));
} elseif (is_array($value) || is_object($value)) {
$value = array_map(array($query, 'quote'), (array) $value);
$query->where($query->quoteName($key) . new QueryElement('IN ()', $value, ','));
} else {
$query->where($query->format('%n = %q', $key, $value));
}
}
return $query;
}
/**
* Method to test getExpression().
*
* @return void
*
* @covers Windwalker\Query\Query::getExpression
*/
public function testGetExpression()
{
$this->assertInstanceOf('Windwalker\\Query\\QueryExpression', $this->instance->getExpression());
}
/**
* Clear data from the query or a specific clause of the query.
*
* @param string $clause Optionally, the name of the clause to clear, or nothing to clear the whole query.
*
* @return SqliteQuery Returns this object to allow chaining.
*
* @since 2.0
*/
public function clear($clause = null)
{
switch ($clause) {
case null:
$this->bounded = array();
break;
}
return parent::clear($clause);
}
/**
* Process ordering query.
*
* @param Query $query The query object.
* @param string $ordering The ordering string.
* @param string $direction ASC or DESC.
*
* @return void
*/
protected function processOrdering(Query $query, $ordering = null, $direction = null)
{
$ordering = $this->state->get('list.ordering', $ordering);
$this->state->set('list.ordering', $ordering);
// If no ordering set, ignore this function.
if (!$ordering) {
return;
}
$self = $this;
$direction = $this->state->get('list.direction', $direction);
$this->state->set('list.direction', $direction);
$ordering = explode(',', $ordering);
// Add quote
$ordering = array_map(function ($value) use($query, $self) {
// Remove extra spaces
preg_replace('/\\s+/', ' ', $value);
$value = explode(' ', trim($value));
// Check it is an allowed field.
if (!$self->filterField($value[0])) {
return '';
}
$field = $this->mapField($value[0]);
if (!empty($field) && $field[strlen($field) - 1] != ')') {
$field = $query->quoteName($field);
}
// $value[1] is direction
if (isset($value[1])) {
return $field . ' ' . $value[1];
}
return $field;
}, $ordering);
$ordering = array_filter($ordering, 'strlen');
$ordering = implode(', ', $ordering);
if (!$ordering) {
return;
}
$query->order($ordering . ' ' . $direction);
}
/**
* Method to test escape().
*
* @return void
*
* @covers Windwalker\Query\Query::escape
* @covers Windwalker\Query\Query::e
*/
public function testEscape()
{
$this->assertEquals('foo "\'\'_-!@#$%^&*() ' . "\n \t \r" . ' \\0', $this->instance->escape("foo \"'_-!@#\$%^&*() \n \t \r "));
// Use Pdo object to escape.
try {
$pdo = new \PDO('mssql:user=root;');
$query = new Query($pdo);
$this->assertEquals('foo \\"\\\'_-!@#$%^&*() \\n ' . "\t" . ' \\r \\0', $query->escape("foo \"'_-!@#\$%^&*() \n \t \r "));
} catch (\PDOException $e) {
// Driver not found, ignore this test.
}
}
