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