XF 1.5 Is there a way to delete unused tags?

[Alpha1]

I need to delete all content tags that are not used.
Is there a query I can use or an addon that provides this function?

 

[Mike]

This should generally be handled automatically internally.

 

[Alpha1]

I have 800 unused tags that the system is not dealing with.

 

[Mike]

How are you determining their unused? In the control panel? If you click edit on one of them, are they tagged as permanent?

 

[Alpha1]

I determine unused if the total uses in admincp is 0 and if I click on the tag there are no threads on the tag page.
If I click edit on one of them then these are not marked as permanent.

 

[Alpha1]

@Xon was kind enough to provide me with a script to delete unused and low use tags. 15k tags were deleted.

 

[megamuell]

@Xon was kind enough to provide me with a script to delete unused and low use tags. 15k tags were deleted.

Could you please publish this script? i would like to delete all tags with low use and dont want to click 1000 times.

 

[Alpha1]

There now is a function for it in the tag essentials addon.

 

[megamuell]

well a easy free solution would be good too... 30 pounds for one time usage after import...

 

[Alpha1]

You can place a request and ask a developer to work for you for free. Maybe there is a skilled developer who is willing to do that. But I doubt it.

I think 30 pounds is almost for free. The alternative is to go through all tags by hand and delete them one by one. I have done this many times over the years, sometimes working on it for weeks. So I personally much appreciate the cheap functionality that saves me from spending a mass of work on unused tags. I will be using it several times per year.

 

[megamuell]

i tryed with:

DELETE t, tc, trc FROM xf_tag AS t
left JOIN `xf_tag_content` as tc ON t.tag_id=tc.tag_id
left JOIN xf_tag_result_cache AS trc ON t.tag_id=trc.tag_id
WHERE t.use_count < 4

but need to cleanup thread information ...

xf_threads in colum tags its a blob

 

[Alpha1]

I dont think it will work that way. It needs to affect multiple things including the blobs. I hope you took a backup.

 

[megamuell]

well, i have done a cleanup tool. if you have deleted tags with mysql query above.

$page = intval($_GET['p']);
$pp = 350;
$start = $page*$pp;

        $link = mysqli_connect($host,$user,$pass);
        if($link) { if(mysqli_select_db($link,$datb)){
        mysqli_query($link,"SET NAMES 'utf8'");
        mysqli_query($link,"SET CHARACTER SET 'utf8'");
        }
        }

        
    $q = "SELECT thread_id,CONVERT(tags USING utf8) AS tagx, LENGTH(tags) AS tl FROM xf_thread WHERE LENGTH(tags)>5 LIMIT $start,$pp";
    $r = mysqli_query($link,$q);
    
            while($row = mysqli_fetch_assoc($r))
            {
                $row_arr[] = $row;
            }
    
    
    foreach($row_arr AS $row){
        $t = unserialize($row['tagx']);
        $threadID = $row['thread_id'];
        echo "<pre>threadID: ".$threadID." # </pre>";
        foreach($t AS $k => $v){
            if($f = mysqli_fetch_assoc( mysqli_query($link,"SELECT tag_id FROM xf_tag WHERE tag='".$v['tag']."'") ) ){
                echo " tagid_".$f['tag_id']."; ";               
            }else {
                echo "<font color=red> no_tagid_found: ".$v['tag']."; </font>";
                unset($t[$k]); //remove not existing tag
            }           
        }
        $tx = serialize($t);
        
        if($row['tagx']!=$tx){
        //update
            
            $q = "UPDATE xf_thread SET tags='$tx' WHERE thread_id='$threadID' LIMIT 1";
            if(mysqli_query($link,$q)){ echo "-!!-"; }else{ echo "-EE-"; }
        
        echo "<br />".$tx;
        echo "<br />".$row['tagx'];
        }else{           
            echo "---ident!";
        }
        
        echo "<hr />";
        
                
    }
    
    $page++;
    echo "<a href='?p=$page'>NEXT PAGE</a><meta http-equiv=refresh content='1,?p=$page' />";

 

[Xon]

There is a much simpler way to purge tags. And one which doesn't risk SQL injection attacks.

After editing out the exit statement, place this in your webroot, (but don't keep it there!) and when run it will delete all tags with less than 10 uses (change as required).

<?php
exit();

$startTime = microtime(true);

@set_time_limit(0);
ignore_user_abort(true);
$fileDir = dirname(__FILE__);

require($fileDir . '/library/XenForo/Autoloader.php');
XenForo_Autoloader::getInstance()->setupAutoloader($fileDir . '/library');

XenForo_Application::initialize($fileDir . '/library', $fileDir);
XenForo_Application::set('page_start_time', $startTime);

$dependencies = new XenForo_Dependencies_Public();
$dependencies->preLoadData();

$db = XenForo_Application::get('db');

$tags = $db->fetchAll("
select *
from xf_tag
where xf_tag.use_count < 10;
");

print count($tags) . " about to delete in 2 seconds\n";
sleep(2);
print "Now deleting\n";

foreach($tags as $tag)
{
    $dw = XenForo_DataWriter::create('XenForo_DataWriter_Tag');
    $dw->setExistingData($tag);
    $dw->delete();
}

$threads = $db->fetchAll("
    SELECT thread_id
    FROM xf_thread
    WHERE xf_thread.tags <> 'a:0:{}' and xf_thread.tags <> ''
");

/** @var XenForo_Model_Tag $tagModel */
$tagModel = XenForo_Model::create('XenForo_Model_Tag');

foreach ($threads AS $thread)
{
XenForo_Db::beginTransaction($db);
    $tagModel->rebuildTagCache('thread', $thread['thread_id']);
XenForo_Db::commit($db);
}

The version in Tag Essentials is much more complex as it supports and is aware if Tag Essentials features and permanent tags.

This script will permanently delete data, so take a backup of your database before using it.

I can only offer limited support for this.

 

[Biarritz64]

@Xon nice work, can someone make it xf2 compatible?