3

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!

1
  • Welcome to JSE. Please take our tour to get to know all features of Stack Exchange.
    – pe7er
    Feb 11, 2022 at 17:04

1 Answer 1

2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.