Query to find article ID's that have certain tags Joomla 3

Question

I'm trying to get article content_item_id-s from Joomla contentitem_tag_map table, where tag_id equals to one or more values. The problem is that in Joomla (3.5), all tag associations are displayed in different rows, like that:

Query involved columns from contentitem_tag_map table:


     |  content_item_id  |  tag_id  |
     --------------------------------
     |                4  |  7       |
     --------------------------------
     |                8  |  6       |
     --------------------------------
     |                9  |  5       |
     --------------------------------
     |                9  |  9       |
     --------------------------------
     |               11  |  3       |
     --------------------------------
     |               11  |  9       |
     --------------------------------
     |               12  |  3       |
     --------------------------------

My question is, with what query can I get content_item_id-s from this table, if it must have two or more tag_id-s?

For example:

I want to get all content_item_id-s that have tag_id "3" AND "9" (result should be "11")

OR

I want to get all content_item_id-s that have tag_id "5" AND "9" (result should be "9")

My current query is as follows:


     $articleIDQuery = $db->getQuery(true);
     $articleIDQuery->select('distinct content_item_id');
     $articleIDQuery->from($db->quoteName('#__contentitem_tag_map'));
     $articleIDQuery->where($db->quoteName('tag_id')." IN (3,9)");

     $db->setQuery($articleIDQuery);
     $taggedArticleID = $db->loadObjectList();

This query results in displaying content_item_id-s that have tag_id-s "3" OR "9" instead of "3" AND "9"

I would really appreciate if anyone can give me any ideas on how to get the wanted results. I'm pretty much a SQL newbie, so google'ing the answers is way too complicated, since I can't really put my idea into one sentence.

Thank you in advance.

Sandro Chichirichi Cantagallo Sandro Chichirichi Cantagallo · Accepted Answer · 2017-07-28T15:41:44

I use the query below, but is very slow.

SELECT `jc`.*
FROM `dbname`.`joomla_content` AS `jc`
INNER JOIN `dbname`.`joomla_contentitem_tag_map` AS `jctm0` ON `jc`.`id` = `jctm0`.`content_item_id` AND `jctm0`.`tag_id` = 5
INNER JOIN `dbname`.`joomla_contentitem_tag_map` AS `jctm1` ON `jc`.`id` = `jctm1`.`content_item_id` AND `jctm1`.`tag_id` = 9
ORDER BY `jc`.`id` ASC

Selecting more tag ids in the query slows it down. The problem is that the query finds all contents that have 5 and 9 id_tag but also contents that have more tag ids and this two. You must iterate over results and count is tot_tags result is = tags your search.

Or if you are working on a joomla project you can just use JHelperTags for generating your query. Put all tag ids that you want to filter inside an array and then:

$this->query = $tagsHelper->getTagItemsQuery($idTags, null, false, 'c.core_publish_up', 'DESC', false, 'all', '1' );        
$this->db->setQuery($this->query);
$results = $this->db->loadAssocList();

That generate a query like this one:

SELECT m.type_alias, m.content_item_id, m.core_content_id, count(m.tag_id) AS match_count, MAX(m.tag_date) as tag_date, MAX(c.core_title) AS core_title, MAX(c.core_params) AS core_params,MAX(c.core_alias) AS core_alias, MAX(c.core_body) AS core_body, MAX(c.core_state) AS core_state, MAX(c.core_access) AS core_access,MAX(c.core_metadata) AS core_metadata, MAX(c.core_created_user_id) AS core_created_user_id, MAX(c.core_created_by_alias) AS core_created_by_alias,MAX(c.core_created_time) as core_created_time, MAX(c.core_images) as core_images,CASE WHEN c.core_modified_time = '0000-00-00 00:00:00' THEN c.core_created_time ELSE c.core_modified_time END as core_modified_time,MAX(c.core_language) AS core_language, MAX(c.core_catid) AS core_catid,MAX(c.core_publish_up) AS core_publish_up, MAX(c.core_publish_down) as core_publish_down,MAX(ct.type_title) AS content_type_title, MAX(ct.router) AS router,CASE WHEN c.core_created_by_alias > ' ' THEN c.core_created_by_alias ELSE ua.name END AS author,ua.email AS author_email
FROM #__contentitem_tag_map AS m
INNER JOIN #__ucm_content AS c ON m.type_alias = c.core_type_alias AND m.core_content_id = c.core_content_id AND c.core_state IN (1) AND (c.core_publish_up = '0000-00-00 00:00:00' OR c.core_publish_up <= '2017-07-28 15:37:34')  AND (c.core_publish_down = '0000-00-00 00:00:00' OR  c.core_publish_down >= '2017-07-28 15:37:34')
INNER JOIN #__content_types AS ct ON ct.type_alias = m.type_alias
LEFT JOIN #__users AS ua ON ua.id = c.core_created_user_id
WHERE m.tag_id IN (8,83,85) AND m.type_alias IN ('com_content.article','com_contact.contact','com_newsfeeds.newsfeed','com_users.user','com_content.category','com_contact.category','com_newsfeeds.category','com_tags.tag','com_banners.banner','com_banners.category','com_banners.client','com_users.note','com_users.category','com_weblinks.weblink','com_weblinks.category') AND c.core_access IN (0,1)
GROUP BY m.type_alias, m.content_item_id, m.core_content_id
ORDER BY MAX(`c`.`core_publish_up`) DESC

If you use much_count value for filter data you can find on

Query to find article ID's that have certain tags Joomla 3

1 Answers