/** * Magic function to convert the query to a string. * * @return string The completed query. * * @since 1.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; }
/** * Tests the \Joomla\Database\DatabaseQuery::format method. * * @return void * * @covers \Joomla\Database\DatabaseQuery::format * @since 1.0 */ public function testFormat() { $result = $this->instance->format('SELECT %n FROM %n WHERE %n = %a', 'foo', '#__bar', 'id', 10); $expected = 'SELECT ' . $this->instance->qn('foo') . ' FROM ' . $this->instance->qn('#__bar') . ' WHERE ' . $this->instance->qn('id') . ' = 10'; $this->assertThat($result, $this->equalTo($expected), 'Line: ' . __LINE__ . '.'); $result = $this->instance->format('SELECT %n FROM %n WHERE %n = %t OR %3$n = %Z', 'id', '#__foo', 'date'); $expected = 'SELECT ' . $this->instance->qn('id') . ' FROM ' . $this->instance->qn('#__foo') . ' WHERE ' . $this->instance->qn('date') . ' = ' . $this->instance->currentTimestamp() . ' OR ' . $this->instance->qn('date') . ' = ' . $this->instance->nullDate(true); $this->assertThat($result, $this->equalTo($expected), 'Line: ' . __LINE__ . '.'); }
/** * Method to append the primary keys for this table to a query. * * @param DatabaseQuery $query A query object to append. * @param mixed $pk Optional primary key parameter. * * @return $this Method allows chaining * * @since 1.0 */ public function appendPrimaryKeys($query, $pk = null) { if (is_null($pk)) { foreach ($this->tableKeys as $k) { $query->where($this->db->quoteName($k) . ' = ' . $this->db->quote($this->{$k})); } } else { if (is_string($pk)) { $pk = array($this->tableKeys[0] => $pk); } $pk = (object) $pk; foreach ($this->tableKeys as $k) { $query->where($this->db->quoteName($k) . ' = ' . $this->db->quote($pk->{$k})); } } return $this; }
/** * Common function to process the search filter for a query * * @param DatabaseQuery $query DatabaseQuery object * @param string $filter Filter string * * @return DatabaseQuery * * @since 1.0 */ private function processSearchFilter(DatabaseQuery $query, $filter) { $db = $this->getDb(); // Clean filter variable $filter = $db->quote('%' . $db->escape(String::strtolower($filter), true) . '%', false); // Check the author, title, and publish_up fields $query->where('(' . $db->quoteName('a.title') . ' LIKE ' . $filter . ' OR ' . $db->quoteName('a.description') . ' LIKE ' . $filter . ' OR ' . $db->quoteName('a.issue_number') . ' LIKE ' . $filter . ')'); return $query; }
/** * Common function to process the filters for a query based on the model state * * @param DatabaseQuery $query DatabaseQuery object * * @return DatabaseQuery * * @since 1.0 */ private function processStateFilter(DatabaseQuery $query) { $db = $this->getDb(); $filter = $this->getProject()->project_id; if ($filter) { $query->where($db->quoteName('a.project_id') . ' = ' . (int) $filter); } $filter = $this->state->get('filter.search'); if ($filter) { $query = $this->processSearchFilter($query, $filter); } $filter = $this->state->get('filter.status'); if ($filter) { $query->where($db->quoteName('a.status') . ' = ' . (int) $filter); } $filter = $this->state->get('filter.state'); // State == 2 means "all". if (is_numeric($filter) && 2 != $filter) { $query->where($db->quoteName('s.closed') . ' = ' . (int) $filter); } $filter = $this->state->get('filter.priority'); if ($filter) { $query->where($db->quoteName('a.priority') . ' = ' . (int) $filter); } $filter = $this->state->get('filter.user'); if ($filter && is_numeric($filter)) { $username = $this->state->get('username'); switch ($filter) { case 1: $query->where($db->quoteName('a.opened_by') . ' = ' . $db->quote($username)); break; case 2: // Join over the activities. $query->join('LEFT', '#__activities AS ac ON a.issue_number = ac.issue_number'); $query->where($db->quoteName('ac.user') . ' = ' . $db->quote($username)); $query->where($db->quoteName('ac.project_id') . ' = ' . (int) $this->getProject()->project_id); $query->group('a.issue_number'); break; } } $filter = $this->state->get('filter.created_by'); if ($filter) { // Clean filter variable $filter = $db->quote('%' . $db->escape(StringHelper::strtolower($filter), true) . '%', false); $query->where($db->quoteName('a.opened_by') . ' LIKE ' . $filter); } $filter = $this->state->get('filter.category'); if ($filter && is_numeric($filter)) { $categoryModel = new CategoryModel($db); // If the category filter equals -1, that means we want issues without category. if ($filter == -1) { $issues = $categoryModel->getIssueIdsWithCategory(); } else { $issues = $categoryModel->getIssueIdsByCategory($filter); } if ($issues != null) { $issueId = array(); foreach ($issues as $issue) { $issueId[] = $issue->issue_id; } $issueId = implode(', ', $issueId); } else { $issueId = 0; } // Handle the no category filter if ($filter == -1) { $query->where($db->quoteName('a.id') . ' NOT IN (' . $issueId . ')'); } else { $query->where($db->quoteName('a.id') . ' IN (' . $issueId . ')'); } } $filter = $this->state->get('filter.label'); if ($filter && is_numeric($filter)) { $query->where('FIND_IN_SET(' . $filter . ', ' . $db->quoteName('a.labels') . ')'); } $filter = $this->state->get('filter.tests'); if ($filter && is_numeric($filter)) { // Common query elements $query->leftJoin($db->quoteName('#__issues_tests', 'it') . 'ON a.id = it.item_id')->where($db->quoteName('a.has_code') . ' = 1')->group('a.issue_number'); switch ($filter) { case 1: $query->where($db->quoteName('it.result') . ' = 1')->having('COUNT(it.item_id) = 1'); break; case 2: $query->where($db->quoteName('it.result') . ' = 1')->having('COUNT(it.item_id) > 1'); break; case 3: $query->having('COUNT(it.item_id) = 0'); break; } } $filter = $this->state->get('filter.easytest'); if ($filter && is_numeric($filter)) { $query->where($db->quoteName('a.easy') . ' = ' . (int) $filter); } 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 1.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; }
/** * 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 MysqliQuery Returns this object to allow chaining. * * @since __DEPLOY_VERSION__ */ public function clear($clause = null) { switch ($clause) { case null: $this->bounded = array(); break; } return parent::clear($clause); }
/** * buildConditions * * @param DatabaseQuery $query * @param array $conditions * * @return DatabaseQuery */ public static function buildWheres(DatabaseQuery $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; }