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