Query database for articles, filtering by tag

Question

I can use this to get published articles from the database:

function queryDatabase_Posts($select, $limit) {
    // Database connection and query object.
    $db = JFactory::getDbo();
    $query = $db->getQuery(true);
    // Select.
    $query->select($select);
    $query->from($db->quoteName('#__content'));
    // Published posts only.
    $query->where($db->quoteName('state') . ' = 1'); 
    // Order by the date they are published.
    $query->order('publish_up DESC');
    // Set and fetch.
    $db->setQuery($query);
    return $db->loadAssocList();     
}

but according to this overview of the #__content table tags are apparently kept in a different table.

How can I query that table? What is it called, what's inside? Is there documentation on that?

Ultimately I want to query the database for posts that have a certain tag of which I have the name. So I would need to query the tag table for the ID.

Answer 1

You could Join the #__content table with the one of the #__contentitem_tag_map, which is the table that stores the many-to-many relationships among the various content items and the tags:

$query->join('INNER', '#__contentitem_tag_map AS tags ON `tags`.`content_item_id` = `#__content`.`id`');

Then in your query you can filter the content items with a where:

$query->where('(tags.tag_id = '. $yourTag .') AND `tags`.`type_id` = 1');

• In your script, pass your desired tag to the $yourTag variable.

• The tags.type_id = 1 is the type for the tags associated with articles.