/**
* Method to get a query to retrieve a detailed list of items for a tag.
*
* @param mixed $tagId Tag or array of tags to be matched
* @param mixed $typesr Null, type or array of type aliases for content types to be included in the results
* @param boolean $includeChildren True to include the results from child tags
* @param string $orderByOption Column to order the results by
* @param string $orderDir Direction to sort the results in
* @param boolean $anyOrAll True to include items matching at least one tag, false to include
* items all tags in the array.
* @param string $languageFilter Optional filter on language. Options are 'all', 'current' or any string.
* @param string $stateFilter Optional filtering on publication state, defaults to published or unpublished.
*
* @return \Joomla\Database\DatabaseQuery Query to retrieve a list of tags
*
* @since 3.1
*/
public function getTagItemsQuery($tagId, $typesr = null, $includeChildren = false, $orderByOption = 'c.core_title', $orderDir = 'ASC', $anyOrAll = true, $languageFilter = 'all', $stateFilter = '0,1')
{
// Create a new query object.
$db = Factory::getDbo();
$query = $db->getQuery(true);
$user = Factory::getUser();
$nullDate = $db->getNullDate();
$nowDate = Factory::getDate()->toSql();
// Force ids to array and sanitize
$tagIds = (array) $tagId;
$tagIds = implode(',', $tagIds);
$tagIds = explode(',', $tagIds);
$tagIds = ArrayHelper::toInteger($tagIds);
$ntagsr = \count($tagIds);
// If we want to include children we have to adjust the list of tags.
// We do not search child tags when the match all option is selected.
if ($includeChildren) {
$tagTreeArray = array();
foreach ($tagIds as $tag) {
$this->getTagTreeArray($tag, $tagTreeArray);
}
$tagIds = array_values(array_unique(array_merge($tagIds, $tagTreeArray)));
}
// Sanitize filter states
$stateFilters = explode(',', $stateFilter);
$stateFilters = ArrayHelper::toInteger($stateFilters);
// M is the mapping table. C is the core_content table. Ct is the content_types table.
$query->select([$db->quoteName('m.type_alias'), $db->quoteName('m.content_item_id'), $db->quoteName('m.core_content_id'), 'COUNT(' . $db->quoteName('m.tag_id') . ') AS ' . $db->quoteName('match_count'), 'MAX(' . $db->quoteName('m.tag_date') . ') AS ' . $db->quoteName('tag_date'), 'MAX(' . $db->quoteName('c.core_title') . ') AS ' . $db->quoteName('core_title'), 'MAX(' . $db->quoteName('c.core_params') . ') AS ' . $db->quoteName('core_params'), 'MAX(' . $db->quoteName('c.core_alias') . ') AS ' . $db->quoteName('core_alias'), 'MAX(' . $db->quoteName('c.core_body') . ') AS ' . $db->quoteName('core_body'), 'MAX(' . $db->quoteName('c.core_state') . ') AS ' . $db->quoteName('core_state'), 'MAX(' . $db->quoteName('c.core_access') . ') AS ' . $db->quoteName('core_access'), 'MAX(' . $db->quoteName('c.core_metadata') . ') AS ' . $db->quoteName('core_metadata'), 'MAX(' . $db->quoteName('c.core_created_user_id') . ') AS ' . $db->quoteName('core_created_user_id'), 'MAX(' . $db->quoteName('c.core_created_by_alias') . ') AS' . $db->quoteName('core_created_by_alias'), 'MAX(' . $db->quoteName('c.core_created_time') . ') AS ' . $db->quoteName('core_created_time'), 'MAX(' . $db->quoteName('c.core_images') . ') AS ' . $db->quoteName('core_images'), 'CASE WHEN ' . $db->quoteName('c.core_modified_time') . ' = :nullDate THEN ' . $db->quoteName('c.core_created_time') . ' ELSE ' . $db->quoteName('c.core_modified_time') . ' END AS ' . $db->quoteName('core_modified_time'), 'MAX(' . $db->quoteName('c.core_language') . ') AS ' . $db->quoteName('core_language'), 'MAX(' . $db->quoteName('c.core_catid') . ') AS ' . $db->quoteName('core_catid'), 'MAX(' . $db->quoteName('c.core_publish_up') . ') AS ' . $db->quoteName('core_publish_up'), 'MAX(' . $db->quoteName('c.core_publish_down') . ') AS ' . $db->quoteName('core_publish_down'), 'MAX(' . $db->quoteName('ct.type_title') . ') AS ' . $db->quoteName('content_type_title'), 'MAX(' . $db->quoteName('ct.router') . ') AS ' . $db->quoteName('router'), 'CASE WHEN ' . $db->quoteName('c.core_created_by_alias') . ' > ' . $db->quote(' ') . ' THEN ' . $db->quoteName('c.core_created_by_alias') . ' ELSE ' . $db->quoteName('ua.name') . ' END AS ' . $db->quoteName('author'), $db->quoteName('ua.email', 'author_email')])->bind(':nullDate', $nullDate)->from($db->quoteName('#__contentitem_tag_map', 'm'))->join('INNER', $db->quoteName('#__ucm_content', 'c'), $db->quoteName('m.type_alias') . ' = ' . $db->quoteName('c.core_type_alias') . ' AND ' . $db->quoteName('m.core_content_id') . ' = ' . $db->quoteName('c.core_content_id'))->join('INNER', $db->quoteName('#__content_types', 'ct'), $db->quoteName('ct.type_alias') . ' = ' . $db->quoteName('m.type_alias'));
// Join over categories to get only tags from published categories
$query->join('LEFT', $db->quoteName('#__categories', 'tc'), $db->quoteName('tc.id') . ' = ' . $db->quoteName('c.core_catid'));
// Join over the users for the author and email
$query->join('LEFT', $db->quoteName('#__users', 'ua'), $db->quoteName('ua.id') . ' = ' . $db->quoteName('c.core_created_user_id'))->whereIn($db->quoteName('c.core_state'), $stateFilters)->whereIn($db->quoteName('m.tag_id'), $tagIds)->extendWhere('AND', [$db->quoteName('c.core_catid') . ' = 0', $db->quoteName('tc.published') . ' = 1'], 'OR');
// Get the type data, limited to types in the request if there are any specified.
$typesarray = self::getTypes('assocList', $typesr, false);
$typeAliases = \array_column($typesarray, 'type_alias');
$query->whereIn($db->quoteName('m.type_alias'), $typeAliases, ParameterType::STRING);
$groups = array_values(array_unique($user->getAuthorisedViewLevels()));
$groups[] = 0;
$query->whereIn($db->quoteName('c.core_access'), $groups);
if (!\in_array(0, $stateFilters, true)) {
$query->extendWhere('AND', [$db->quoteName('c.core_publish_up') . ' = :nullDate1', $db->quoteName('c.core_publish_up') . ' IS NULL', $db->quoteName('c.core_publish_up') . ' <= :nowDate1'], 'OR')->extendWhere('AND', [$db->quoteName('c.core_publish_down') . ' = :nullDate2', $db->quoteName('c.core_publish_down') . ' IS NULL', $db->quoteName('c.core_publish_down') . ' >= :nowDate2'], 'OR')->bind([':nullDate1', ':nullDate2'], $nullDate)->bind([':nowDate1', ':nowDate2'], $nowDate);
}
// Optionally filter on language
if ($languageFilter !== 'all') {
if ($languageFilter === 'current_language') {
$languageFilter = $this->getCurrentLanguage();
}
$query->whereIn($db->quoteName('c.core_language'), [$languageFilter, '*'], ParameterType::STRING);
}
$query->group([$db->quoteName('m.type_alias'), $db->quoteName('m.content_item_id'), $db->quoteName('m.core_content_id'), $db->quoteName('core_modified_time'), $db->quoteName('core_created_time'), $db->quoteName('core_created_by_alias'), $db->quoteName('author'), $db->quoteName('author_email')]);
// Use HAVING if matching all tags and we are matching more than one tag.
if ($ntagsr > 1 && $anyOrAll != 1 && $includeChildren != 1) {
// The number of results should equal the number of tags requested.
$query->having('COUNT(' . $db->quoteName('m.tag_id') . ') = :ntagsr')->bind(':ntagsr', $ntagsr, ParameterType::INTEGER);
}
// Set up the order by using the option chosen
if ($orderByOption === 'match_count') {
$orderBy = 'COUNT(' . $db->quoteName('m.tag_id') . ')';
} else {
$orderBy = 'MAX(' . $db->quoteName($orderByOption) . ')';
}
$query->order($orderBy . ' ' . $orderDir);
return $query;
}