5

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.

3

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.

  • Thanks for your answer. Do you happen to know where the tags are stored? I am trying to access all tags there are - so no connection to any article needs to be given. – kunterbunt Dec 10 '15 at 20:54
  • Nevermind, connecting to the database and looking at the tables showed me it's just #__tags... – kunterbunt Dec 10 '15 at 21:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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