Actually, I've created a counter for the tags in Joomla 4. My PR https://github.com/joomla/joomla-cms/pull/28831
To get a list of tags with the number of articles, you need to
connect the tables:
- #__tags - for the tag names
- #__contentitem_tag_map - as a sub query to count the tags
My PR was for all kinds of tags, so not only for articles. Maybe you could use the following query as a basis. To only count articles, you could add some where
statement to filter the contentitem_tag_map
on type_alias
= com_content.article
.
// Create a new query object.
$db = $this->getDbo();
$query = $db->getQuery(true);
// Select the required fields from the table.
$query->select(
$this->getState(
'list.select',
'a.id, a.title, a.alias, a.note, a.published, a.access, a.description' .
', a.checked_out, a.checked_out_time, a.created_user_id' .
', a.path, a.parent_id, a.level, a.lft, a.rgt' .
', a.language'
)
);
$query->from($db->quoteName('#__tags', 'a'))
->where($db->quoteName('a.alias') . ' <> ' . $db->quote('root'));
// Join over the language
$query->select(
[
$db->quoteName('l.title', 'language_title'),
$db->quoteName('l.image', 'language_image'),
]
)
->join('LEFT', $db->quoteName('#__languages', 'l'), $db->quoteName('l.lang_code') . ' = ' . $db->quoteName('a.language'));
// Join over the users for the checked out user.
$query->select($db->quoteName('uc.name', 'editor'))
->join('LEFT', $db->quoteName('#__users', 'uc'), $db->quoteName('uc.id') . ' = ' . $db->quoteName('a.checked_out'));
// Join over the users for the author.
$query->select($db->quoteName('ua.name', 'author_name'))
->join('LEFT', $db->quoteName('#__users', 'ua'), $db->quoteName('ua.id') . ' = ' . $db->quoteName('a.created_user_id'))
->select($db->quoteName('ug.title', 'access_title'))
->join('LEFT', $db->quoteName('#__viewlevels', 'ug'), $db->quoteName('ug.id') . ' = ' . $db->quoteName('a.access'));
// Count Items
$subQueryCountTaggedItems = $db->getQuery(true);
$subQueryCountTaggedItems
->select('COUNT(' . $db->quoteName('tag_map.content_item_id') . ')')
->from($db->quoteName('#__contentitem_tag_map', 'tag_map'))
->where($db->quoteName('tag_map.tag_id') . ' = ' . $db->quoteName('a.id'));
$query->select('(' . (string) $subQueryCountTaggedItems . ') AS ' . $db->quoteName('countTaggedItems'));
$db->setQuery($query);
$results = $db->loadObjectList();