/**
* Tests the JDatabaseQuery::group method.
*
* @return void
*
* @since 11.3
*/
public function testGroup()
{
$this->assertThat($this->_instance->group('foo'), $this->identicalTo($this->_instance), 'Tests chaining.');
$this->assertThat(trim($this->_instance->group), $this->equalTo('GROUP BY foo'), 'Tests rendered value.');
// Add another column.
$this->_instance->group('bar');
$this->assertThat(trim($this->_instance->group), $this->equalTo('GROUP BY foo,bar'), 'Tests rendered value after second use.');
}
/**
* The post getQuery object.
*
* @param JDatabaseQuery $query The db query object.
*
* @return void
*/
protected function postGetQuery(\JDatabaseQuery $query)
{
$keys = $this->state->get('profileKeys');
// Build SQL Pivot
// ========================================================================
foreach ($keys as $key) {
if ($key) {
/*
* Use MySQL Pivot query:
* MAX(IF(profile.key = 'foo', profile.value, NULL)) AS foo
*/
$query->select($query->format("MAX(IF(profile.key = %q, profile.value, NULL)) AS %e", $key, $key));
}
}
$query->group('user.id');
}
/**
* Implements custom filtering
*
* @param \JDatabaseQuery $query The model query we're operating on
* @param bool $overrideLimits Are we told to override limits?
*
* @return void
*/
protected function onBeforeBuildQuery(\JDatabaseQuery &$query, $overrideLimits = false)
{
$db = $this->getDbo();
$logCategoryLimit = $this->getState('logCategoryLimit', 0, 'int');
$fltCategory = null;
/**
* In the back-end of the component when the model state 'logCategoryLimit' is set to 1 we will get the default
* category ID to filter by from the 'category' state variable of the Logs model. This allows us to do a
* drill-down search in the logs using nothing but XML forms.
*/
if ($this->container->platform->isBackend() && $logCategoryLimit) {
/** @var Categories $logsModel */
$logsModel = $this->container->factory->model('Logs');
$fltCategory = $logsModel->getState('category', null, 'int');
}
$fltCategory = $this->getState('category_id', $fltCategory, 'int');
$fltCategory = $this->getState('category', $fltCategory, 'int');
if ($fltCategory > 0) {
$query->where($db->qn('category_id') . ' = ' . $db->q($fltCategory));
}
$fltAccessUser = $this->getState('access_user', null, 'int');
if (!is_null($fltAccessUser)) {
$access_levels = $this->container->platform->getUser($fltAccessUser)->getAuthorisedViewLevels();
if (empty($access_levels)) {
// Essentially, tell it to find nothing if no our user is authorised to no access levels
$access_levels = [$db->q(-1)];
}
$access_levels = array_map(array($db, 'quote'), $access_levels);
// Filter this table
$query->where('(' . '(' . $db->qn('access') . ' IN (' . implode(',', $access_levels) . ')) OR (' . $db->qn('show_unauth_links') . ' = ' . $db->q(1) . '))');
// Filter the categories table, too
$this->whereHas('category', function (\JDatabaseQuery $subQuery) use($access_levels, $db) {
$subQuery->where($db->qn('access') . ' IN (' . implode(',', $access_levels) . ')');
});
}
$fltNoBEUnpub = $this->getState('nobeunpub', null, 'int');
if ($fltNoBEUnpub) {
// Filter this table
$query->where($db->qn('published') . ' != ' . $db->q(0));
// Filter the categories table, too
$this->whereHas('category', function (\JDatabaseQuery $subQuery) use($db) {
$subQuery->where('NOT(' . $db->qn('published') . ' = ' . $db->q('0') . ' AND ' . $db->qn('type') . '=' . $db->q('bleedingedge') . ')');
});
}
$fltLanguage = $this->getState('language', null, 'cmd');
$fltLanguage2 = $this->getState('language2', null, 'string');
if ($fltLanguage != '*' && $fltLanguage != '') {
// Filter this table
$query->where($db->qn('language') . ' IN (' . $db->q('*') . ',' . $db->q($fltLanguage) . ')');
// Filter the categories table, too
$this->whereHas('category', function (\JDatabaseQuery $subQuery) use($db, $fltLanguage) {
$subQuery->where($db->qn('language') . ' IN (' . $db->q('*') . ',' . $db->q($fltLanguage) . ')');
});
} elseif ($fltLanguage2) {
// Filter this table
$query->where($db->qn('language') . ' = ' . $db->q($fltLanguage2));
// Filter the categories table, too
$this->whereHas('category', function (\JDatabaseQuery $subQuery) use($db, $fltLanguage2) {
$subQuery->where($db->qn('language') . ' = ' . $db->q($fltLanguage2));
});
}
$fltMaturity = $this->getState('maturity', 'alpha', 'cmd');
switch ($fltMaturity) {
case 'beta':
$query->where($db->qn('maturity') . ' IN (' . $db->q('beta'), ',' . $db->q('rc') . ',' . $db->q('stable') . ')');
break;
case 'rc':
$query->where($db->qn('maturity') . ' IN (' . $db->q('rc') . ',' . $db->q('stable') . ')');
break;
case 'stable':
$query->where($db->qn('maturity') . ' = ' . $db->q('stable'));
break;
}
// Latest version filter. Use as $releases->published(1)->latest(true)->get(true)
$fltLatest = $this->getState('latest', false, 'bool');
if ($fltLatest) {
// Why just a DESC group by clause? See http://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group
$query->group($db->qn('category_id') . ' DESC');
}
// Order filtering
$fltOrderBy = $this->getState('orderby_filter', null, 'cmd');
switch ($fltOrderBy) {
case 'alpha':
$this->setState('filter_order', 'version');
$this->setState('filter_order_Dir', 'ASC');
break;
case 'ralpha':
$this->setState('filter_order', 'version');
$this->setState('filter_order_Dir', 'DESC');
break;
case 'created':
$this->setState('filter_order', 'created');
$this->setState('filter_order_Dir', 'ASC');
break;
case 'rcreated':
$this->setState('filter_order', 'created');
$this->setState('filter_order_Dir', 'DESC');
break;
case 'order':
$this->setState('filter_order', 'ordering');
$this->setState('filter_order_Dir', 'ASC');
break;
}
}
protected function _load($id)
{
$db = JFactory::getDbo();
$app = JFactory::getApplication();
$user = JFactory::getUser();
$extension = $this->_extension;
// Record that this $id has been checked
$this->_checkedCategories[$id] = true;
$query = new JDatabaseQuery();
// right join with c for category
$query->select('c.*');
$query->select('CASE WHEN CHAR_LENGTH(c.alias) THEN CONCAT_WS(":", c.id, c.alias) ELSE c.id END as slug');
$query->from('#__categories as c');
$query->where('(c.extension=' . $db->Quote($extension) . ' OR c.extension=' . $db->Quote('system') . ')');
if ($this->_options['access']) {
$query->where('c.access IN (' . implode(',', $user->getAuthorisedViewLevels()) . ')');
}
if ($this->_options['published'] == 1) {
$query->where('c.published = 1');
}
$query->order('c.lft');
// s for selected id
if ($id != 'root') {
// Get the selected category
$query->leftJoin('#__categories AS s ON (s.lft <= c.lft AND s.rgt >= c.rgt) OR (s.lft > c.lft AND s.rgt < c.rgt)');
$query->where('s.id=' . (int) $id);
}
$subQuery = ' (SELECT cat.id as id FROM #__categories AS cat JOIN #__categories AS parent ' . 'ON cat.lft BETWEEN parent.lft AND parent.rgt WHERE parent.extension = ' . $db->quote($extension) . ' AND parent.published != 1 GROUP BY cat.id) ';
$query->leftJoin($subQuery . 'AS badcats ON badcats.id = c.id');
$query->where('badcats.id is null');
// i for item
if (isset($this->_options['countItems']) && $this->_options['countItems'] == 1) {
if ($this->_options['published'] == 1) {
$query->leftJoin($db->nameQuote($this->_table) . ' AS i ON i.' . $db->nameQuote($this->_field) . ' = c.id AND i.' . $this->_statefield . ' = 1');
} else {
$query->leftJoin($db->nameQuote($this->_table) . ' AS i ON i.' . $db->nameQuote($this->_field) . ' = c.id');
}
$query->select('COUNT(i.' . $db->nameQuote($this->_key) . ') AS numitems');
}
// Group by
$query->group('c.id');
// Filter by language
if ($app->isSite() && $app->getLanguageFilter()) {
$query->where('(' . ($id != 'root' ? 'c.id=s.id OR ' : '') . 'c.language in (' . $db->Quote(JFactory::getLanguage()->getTag()) . ',' . $db->Quote('*') . '))');
}
// Get the results
$db->setQuery($query);
$results = $db->loadObjectList('id');
$childrenLoaded = false;
if (count($results)) {
// foreach categories
foreach ($results as $result) {
// Deal with root category
if ($result->id == 1) {
$result->id = 'root';
}
// Deal with parent_id
if ($result->parent_id == 1) {
$result->parent_id = 'root';
}
// Create the node
if (!isset($this->_nodes[$result->id])) {
// Create the JCategoryNode and add to _nodes
$this->_nodes[$result->id] = new JCategoryNode($result, $this);
// If this is not root, and if the current nodes parent is in the list or the current node parent is 0
if ($result->id != 'root' && (isset($this->_nodes[$result->parent_id]) || $result->parent_id == 0)) {
// Compute relationship between node and its parent - set the parent in the _nodes field
$this->_nodes[$result->id]->setParent($this->_nodes[$result->parent_id]);
}
// if the node's parent id is not in the _nodes list and the node is not root (doesn't have parent_id == 0),
// then remove this nodes from the list
if (!(isset($this->_nodes[$result->parent_id]) || $result->parent_id == 0)) {
unset($this->_nodes[$result->id]);
continue;
}
if ($result->id == $id || $childrenLoaded) {
$this->_nodes[$result->id]->setAllLoaded();
$childrenLoaded = true;
}
} else {
if ($result->id == $id || $childrenLoaded) {
// Create the JCategoryNode
$this->_nodes[$result->id] = new JCategoryNode($result, $this);
if ($result->id != 'root' && (isset($this->_nodes[$result->parent_id]) || $result->parent_id)) {
// Compute relationship between node and its parent
$this->_nodes[$result->id]->setParent($this->_nodes[$result->parent_id]);
}
if (!isset($this->_nodes[$result->parent_id])) {
unset($this->_nodes[$result->id]);
continue;
}
if ($result->id == $id || $childrenLoaded) {
$this->_nodes[$result->id]->setAllLoaded();
$childrenLoaded = true;
}
}
}
}
} else {
$this->_nodes[$id] = null;
}
}
/**
* The post getQuery object.
*
* @param JDatabaseQuery $query The db query object.
*
* @return void
*/
protected function postGetQuery(\JDatabaseQuery $query)
{
$query->group('module.id');
}