/**
* Tests the \Joomla\Database\DatabaseQuery::andWhere method.
*
* @return void
*
* @since 1.3.0
*/
public function testAndWhere()
{
$this->assertThat($this->instance->where('foo = 1')->andWhere('bar = 2'), $this->identicalTo($this->instance), 'Tests chaining.');
$this->assertThat(trim(TestHelper::getValue($this->instance, 'where')), $this->equalTo('WHERE ' . PHP_EOL . '(foo = 1) AND ' . PHP_EOL . '(bar = 2)'), 'Tests rendered value.');
// Add another set of where conditions.
$this->instance->andWhere(array('baz = 3', 'goo = 4'));
$this->assertThat(trim(TestHelper::getValue($this->instance, 'where')), $this->equalTo('WHERE ' . PHP_EOL . '(' . PHP_EOL . '(foo = 1) AND ' . PHP_EOL . '(bar = 2)) AND ' . PHP_EOL . '(baz = 3 OR goo = 4)'), 'Tests rendered value after second use and array input.');
// Add another set of where conditions with some different glue.
$this->instance->andWhere(array('faz = 5', 'gaz = 6'), 'XOR');
$this->assertThat(trim(TestHelper::getValue($this->instance, 'where')), $this->equalTo('WHERE ' . PHP_EOL . '(' . PHP_EOL . '(' . PHP_EOL . '(foo = 1) AND ' . PHP_EOL . '(bar = 2)) AND ' . PHP_EOL . '(baz = 3 OR goo = 4)) AND ' . PHP_EOL . '(faz = 5 XOR gaz = 6)'), 'Tests rendered value after third use, array input and different glue.');
}
/**
* Tests the \Joomla\Database\DatabaseQuery::where method.
*
* @return void
*
* @covers \Joomla\Database\DatabaseQuery::where
* @since 1.0
*/
public function testWhere()
{
$this->assertThat($this->instance->where('foo = 1'), $this->identicalTo($this->instance), 'Tests chaining.');
$this->assertThat(trim(TestHelper::getValue($this->instance, 'where')), $this->equalTo('WHERE foo = 1'), 'Tests rendered value.');
// Add another column.
$this->instance->where(array('bar = 2', 'goo = 3'));
$this->assertThat(trim(TestHelper::getValue($this->instance, 'where')), $this->equalTo('WHERE foo = 1 AND bar = 2 AND goo = 3'), 'Tests rendered value after second use and array input.');
// Clear the where
TestHelper::setValue($this->instance, 'where', null);
$this->instance->where(array('bar = 2', 'goo = 3'), 'OR');
$this->assertThat(trim(TestHelper::getValue($this->instance, 'where')), $this->equalTo('WHERE bar = 2 OR goo = 3'), 'Tests rendered value with glue.');
}
/**
* 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;
}
/**
* 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;
}
