How can I get a list of the most recent tags in a wordpress database?

2 comments

1. This ain’t pretty, maybe a MySQL junkie can optimize it a little;

   SELECT $wpdb->terms.term_id, $wpdb->terms.name, $wpdb->terms.slug
   FROM $wpdb->terms
       INNER JOIN $wpdb->term_taxonomy ON ($wpdb->terms.term_id = $wpdb->term_taxonomy.term_id)
       INNER JOIN $wpdb->term_relationships ON ($wpdb->terms.term_id = $wpdb->term_relationships.term_taxonomy_id)
       INNER JOIN $wpdb->posts ON ($wpdb->term_relationships.object_id = $wpdb->posts.ID)
   WHERE $wpdb->term_taxonomy.taxonomy = 'post_tag'
   ORDER BY $wpdb->posts.post_date DESC
   

   This basically joins the term, term taxonomy, term relationship and post tables together, getting terms that are of the taxonomy ‘post_tag’, and currently have a relation with a post, then ordering them by the post’s date, descending.

   You might get the same term in the result set multiple times, but I can’t work out how to use GROUP BY or HAVING to fix this, without messing up the date order.

   So an example might in use might be;

   $tags= $wpdb->get_results($query); // $query being the above SQL
   foreach ($tags as $tag) {
       if (!isset($stack[$tag->term_id]))
           $stack[$tag->term_id] = $tag;
   }
   print_r($stack); // should print an array of all tags, ordered by last used
   

   Log in to Reply

2. SELECT name, slug, tag_history.tagdate
   FROM (SELECT wp_term_relationships.term_taxonomy_id AS tagid,
       substr(wp_posts.post_date_gmt,1,10) AS tagdate
       FROM wp_term_relationships 
       INNER JOIN wp_term_taxonomy ON wp_term_taxonomy.term_taxonomy_id=wp_term_relationships.term_taxonomy_id 
       INNER JOIN wp_posts ON wp_posts.ID=wp_term_relationships.object_id 
       WHERE taxonomy='post_tag' ORDER BY post_date_gmt DESC, wp_posts.post_title)
   AS tag_history
   INNER JOIN wp_terms ON wp_terms.term_id=tag_history.tagid
   GROUP BY tag_history.tagid
   ORDER BY tag_history.tagdate DESC
   

   This is what I wrote as a workaround – sure it’s possible to make it sharper! – to build a tag sitemap (XML, needing <LOC&> and <LASTMOD>…) based on the date of association tag<->post. You could add, at the end of the query, a LIMIT 0,10 to view only the 10 last associated tags.

   This query will return three columns (name, slug and date of LAST association of single tag) you might use in many ways.

   Remember that wp_ is the default dB prefix in WordPress. You have to change every occurrence with $wpdb-> (see TheDeadMedic’s answer) and be sure to set $wpdb before the query.

   Log in to Reply