Get records in category and filter them by tags

You can add to $api->records() last parameter $ids. To get those ids

// If you know tag IDs:)

$query = "SELECT record_id FROM #__js_res_tags_history WHERE tag_id IN(2,3)";

// if you do not know tag IDs

$query = "SELECT record_id FROM #__js_res_tags_history WHERE tag_id IN(

SELECT id FROM #__js_res_tags WHERE LOWER(`tag`) = 'my tag'

)";

$db->setQuery($query);

$ids = $db->loadColumns();

Thank you very much Sergey!!! :D

This is how I did it.

$query = 'SELECT record_id FROM #__js_res_tags_history WHERE tag_id IN('.implode(",",$tags_by_id).')'; 



$db->setQuery($query);

$record_ids = $db->loadColumn(); 

$result = $api->records( 

    $params->get('section_id'),

    $params->get('view_what', 'all'),

    $params->get('orderby'),

    $params->get('types', 0),

    $user_id,

    false,

    $params->get('limit', 0),

    false,

    false,

    false,

    $params->get('lang_mode', 0),

    $record_ids);

This returns all record_ids that have any of the tab id's. What I want is to only get the records that has all of the tags.

This is how I did it to only get the records that has all the tags.

$query = 'SELECT record_id, count(record_id) FROM #__js_res_tags_history WHERE tag_id IN('.implode(",",$tags_by_id).') GROUP BY record_id'; 



$db->setQuery($query);

$records = $db->loadRowList();

$record_ids= array();

foreach($records as $record) {

    if ($record[1]==count($tags_by_id)) {

        array_push($record_ids,$record[0]);

    }            

}

$result = $api->records( 

    $params->get('section_id'),

    $params->get('view_what', 'all'),

    $params->get('orderby'),

    $params->get('types', 0),

    $user_id,

    false,

    $params->get('limit', 0),

    false,

    false,

    false,

    $params->get('lang_mode', 0),

    $record_ids);

Do you have a better solution or any comments, please feel free to share!

Notes:

The setUserState function is not used and I still don't understand how to work with it.

The category_id is no longer necessary.

The section_id still necessary.

I do not know why but first example should work.

SELECT record_id FROM #__js_res_tags_history WHERE tag_id IN(1,2,3)

Should already return IDs of only records that has this tag.

By the way you might want to add section_id

SELECT record_id FROM #__js_res_tags_history WHERE tag_id IN(1,2,3) AND section_id = 7;

Sometimes if this query return null API may show all the records. So simple solution to fix.

$query = 'SELECT record_id FROM #__js_res_tags_history WHERE tag_id IN('. implode(",",$tags_by_id).')';



$db->setQuery($query);

$record_ids = $db->loadColumn();

$record_ids[] = 0;

By adding 0 value element to array you make it non empty and at the same time nothing will be returned. Although better approach would be

$query = 'SELECT record_id FROM #__js_res_tags_history WHERE tag_id IN('. implode(",",$tags_by_id).')';



$db->setQuery($query);

$record_ids = $db->loadColumn();

if(!empty($record_ids)) {

    // Process API here.

}

The setUserState function is not used and I still don't understand how to work with it.

User state is not ideal to be used. It will set state for section and then on the other page you will see filtered records without setting any filters.

Anyway state key would be

'com_cobalt.section' . $key . '.filter_tag'

Where $key is

$key = FilterHelper::key();

But again I strongly do not recommend to use this method as it may produce unpredictable results in on other pages.

The setUserState function is not used and I still don't understand how to work with it.

The category_id is no longer necessary.

It is still necessary if you want to show records of only one category. When you get record IDs from tags history, you only define scope. I mean there are IDs of records in all categories of the section. And only in API category is applied.

Sorry Sergey maybe I was unclear, your code worked! I just posted my refinement of it. But thanks for clearing some other things up for me!

Under here you can see the working code we are using for the moment. Later we might also add a function to exclude records that have an specific tags.

Once again, thank you for your support and for the development of Cobalt!

my best ~ Pontus

    private function getRecordsByTags($section_id,$tags) {

        if(empty($tags)) {

            return false;

        }



        $tags_by_id = array();

        $db = JFactory::getDbo();



        foreach($tags as $tag) {

            $sql = 'SELECT id FROM #__js_res_tags WHERE tag = "' . $tag . '"';

            $db->setQuery($sql);

            $result = $db->loadObject();

            $tags_by_id[]=$result->id;

        }



        ArrayHelper::clean_r($tags_by_id);

        JArrayHelper::toInteger($tags_by_id);



        if(empty($tags_by_id)) {

            return false;

        }



        $params = new JRegistry;

        $params->set('section_id', $section_id);

        $current_user = &JFactory::getUser();

        $user_id = $current_user -> id;



        $query = 'SELECT record_id FROM (SELECT record_id, count(record_id) as tags FROM #__js_res_tags_history WHERE tag_id IN('.implode(",",$tags_by_id).') AND section_id = '.$section_id.' GROUP BY record_id) x WHERE x.tags = '.count($tags_by_id); 

        $db->setQuery($query);        

        $record_ids = $db->loadColumn();



        include_once JPATH_ROOT. DIRECTORY_SEPARATOR .'components'. DIRECTORY_SEPARATOR .'com_cobalt'. DIRECTORY_SEPARATOR .'api.php';

        $api = new CobaltApi();

        $result = $api->records( 

            $params->get('section_id'),

            $params->get('view_what', 'all'),

            $params->get('orderby'),

            $params->get('types', 0),

            $user_id,

            false,

            $params->get('limit', 0),

            false,

            false,

            false,

            $params->get('lang_mode', 0),

            $record_ids);



        return $result;

    }

private function getRecordsByTags($section_id, $tags)

    {

        if(empty($tags))

        {

            return FALSE;

        }

        $db = JFactory::getDbo();

        $sql = "SELECT th.record_id FROM #__js_res_tags_history AS th WHERE th.section_id = {$section_id} AND th.tag_id IN(SELECT t.id FROM #__js_res_tags AS t WHERE t.tag IN('".implode("','", $tags)."'))";

        $db->setQuery($sql);

        $record_ids = $db->loadColumn();

        $record_ids[] = 0;

        $record_ids = array_unique($record_ids);

        include_once JPATH_ROOT . '/components/com_cobalt/api.php';

        $api    = new CobaltApi();

        $result = $api->records(

            $section_id,

            'all',

            'r.ctime ACS',

            0, // types

            JFactory::getUser()->get('id'),

            FALSE,

            20, // limit

            FALSE,

            FALSE,

            FALSE,

            0, // language mode

            $record_ids);

        return $result;

    }