Count the number of articles tagged with the same tag

Question

On my Joomla website, I'd like to count all the articles by tag.

I found this thread about how to count the number of articles by category : How to Show number of published articles?

I tried to find the tag in my Joomla website's database to adapt the code, but I couldn't find the tag list in clear. Does anyone know in which table thy are?

Thanks!

Answer 1

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();