How to find out the terms which are not being used for some duration

Question

I have vocabulary and it contains more terms, so I want to implement purge logic on it. i.e. I want to delete all the terms which are not used for some duration say 1 week.

Example

term1 was associated to node1 on Oct 1st term2 was associated to node1 on Sep 30 term1 was associated to node2 on Oct 5th

So now term 2 was not being used for last one week, so now I need to delete that term alone.

I have tried following query,

SELECT ti.tid, td.name, ti.nid
FROM taxonomy_index ti
JOIN taxonomy_term_data td ON ( td.tid = ti.tid ) 
WHERE td.vid =48
AND ti.created < UNIX_TIMESTAMP( DATE_SUB( NOW( ) , INTERVAL 2 WEEK ) ) 

However this query gives me both term1 and term2

Answer 1

Actually the reason for that is that the ti.created column is "The Unix timestamp when the node was created." It does not track the time when the tag was applied to the node, rather the time the node was created.

So that will always have the same value for each row that describes the tag associated with any one node. Look at this screenshot of my taxonomy_index table that displays three different tags I just applied to my node at different times:

From what I can tell, if you wanted that functionality, you'd have to store that timestamp (when a node was actually tagged) in your custom module yourself and you'd have to query that value.

Anyway, hope this helps clarify the issues a little. :-)

Answer 2

You will need a custom module which will track term usage.

file my_module.install

/**
* Implements hook_install()
*/
function my_module_install() {
    $spec = array(
        'description' => 'Last used timestamp',
            'type' => 'int',
            'not null' => TRUE,
            'default' => 0
          );
        db_add_field('taxonomy_index', 'used', $spec);
    }
    return true;
}

file `my_module.module`

function my_module_node_update($node) {
    //read all fields created by taxonomy module
    $term_fields = array_intersect_key(field_info_instances('node', $node->type), field_read_fields(array('module' => 'taxonomy')));
    // array for storing added terms
    $diff_terms = array();
    foreach ($term_fields as $field_name => $field) {
        $new_terms = $old_terms = array();
        // detect language
        $field_langcode = field_language('node', $node, $field_name, LANGUAGE_NONE);
        // we need onli "tid" field
        foreach ($node->{$field_name}[$field_langcode] as $term) {
            $new_terms[] = $term['tid'];
        }
        foreach ($node->original->{$field_name}[$field_langcode] as $term) {
            $old_terms[] = $term['tid'];
        }

        $diff_terms += array_diff($new_terms, $old_terms);
    }
    // update table - set current timestamp 
    db_update('taxonomy_index')
            ->fields(array('used' => time(),))
            ->condition('taxonomy_index.tid', $diff_terms, 'IN')
            ->execute();
}

This will update all terms, used by nodes. In table "taxonomy_index" you'll get new field with last updated timestamp

After you can use hook_cron to delete "old" terms.