/** * 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. } }