Display list of most used tags in the last 30 days

0 Views

I am looking for a way to display the most used tags over the last 30 days. I am no coder, but I have come up with this mashup to display a list of the most used 28 tags (preference to fit my theme). I cannot, for the life of me, figure out how to limit the tags to the most used in the last 30 days.

Here is what I have:

Read More
<ul id="footer-tags">
    <?php
        $tags = get_tags( array('orderby' => 'count', 'order' => 'DESC', 'number'=>28) );
            foreach ( (array) $tags as $tag ) {
            echo '<li><a href="' . get_tag_link ($tag->term_id) . '" rel="tag">' . $tag->name . '</a></li>';
            }
    ?>
</ul>

Update

This is the full code I am using right now, following One Trick Pony’s code below. 

<ul id="footer-tags">
<?php
global $wpdb;
$term_ids = $wpdb->get_col("
   SELECT DISTINCT term_taxonomy_id FROM $wpdb->term_relationships
      INNER JOIN $wpdb->posts ON $wpdb->posts.ID = object_id
      WHERE DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= $wpdb->posts.post_date");

if(count($term_ids) > 0){

  $tags = get_tags(array(
    'orderby' => 'count',
    'order'   => 'DESC',
    'number'  => 28,
    'include' => $term_ids,
  ));
foreach ( (array) $tags as $tag ) {
echo '<li><a href="' . get_tag_link ($tag->term_id) . '" rel="tag">' . $tag->name . '</a></li>';
}
}
?>
</ul>

The website is slightly less than ~4 weeks old, so to test, I changed INTERVAL 30 DAY to INTERVAL 3 DAY and the tags being returned seem random and some haven’t been used in 2+ weeks and have only been used a single time. As well, only 8 tags are being displayed, when more have been used.

To check that the correct number of days have been queried, I did the following:

Completely deleted all items in the trash for posts and pages, I don’t have any custom post types.

Did the same with drafts.

Ran a query in phpmyadmin to delete all post revisions – DELETE FROM wp_posts WHERE post_type = "revision";

Ran a query in phpmyadmin to check if the results are the posts from the last 3 days – SELECT * from wp_posts WHERE DATE_SUB(CURDATE(), INTERVAL 3 DAY) <= post_date

The results from the phpmyadmin query were, in fact, the posts from the last 3 days, but the front-end display did not change.

Update 2

Here are some screen shots. Maybe the screenshots can help find where my code is wrong.

Blog Post with Category and Tags

Image of blog post

wp_posts table with the post ID of above post

wp_posts

wp_terms table with the term_id of the tags used

wp_terms

wp_term_taxonomy with the tags’ term_id as term_taxonomy_id

wp_term_taxonomy

wp_term_relationships with term_taxonomy_id assigned to post as object_id

wp_term_relationships

Update 3

I think I figured out the problem, but do not know how to fix it.

The SQL query gets the term_taxonomy_id, not the actual tag ID and get_tag_link uses term_id

Post Views: 0

Related posts

Leave a Reply

5 comments

  1. The problem was that the SQL query code was getting the term_taxonomy_id, not the actual tag ID.

    I added an additional INNER JOIN using the term_taxonomy table to get the term_id. This seems to work, but if a mod can improve this, please do!

    <ul id="footer-tags">
<?php $wpdb->show_errors(); ?> 
<?php
global $wpdb;
$term_ids = $wpdb->get_col("
    SELECT term_id FROM $wpdb->term_taxonomy
    INNER JOIN $wpdb->term_relationships ON $wpdb->term_taxonomy.term_taxonomy_id=$wpdb->term_relationships.term_taxonomy_id
    INNER JOIN $wpdb->posts ON $wpdb->posts.ID = $wpdb->term_relationships.object_id
    WHERE DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= $wpdb->posts.post_date");

if(count($term_ids) > 0){

  $tags = get_tags(array(
    'orderby' => 'count',
    'order'   => 'DESC',
    'number'  => 28,
    'include' => $term_ids,
  ));
foreach ( (array) $tags as $tag ) {
echo '<li><a href="' . get_tag_link ($tag->term_id) . '" rel="tag">' . $tag->name . '</a></li>';
}
}
?>
</ul>

  2. This query should help you get you the term IDs used by posts from the last 30 days:

    SELECT DISTINCT term_taxonomy_id FROM $wpdb->term_relationships    
  INNER JOIN $wpdb->posts ON $wpdb->posts.ID = object_id    
  WHERE DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= $wpdb->posts.post_date

    Then you pass these IDs to the “include” argument of get_tags()

    Full code:

    global $wpdb;
$term_ids = $wpdb->get_col("
   SELECT DISTINCT term_taxonomy_id FROM $wpdb->term_relationships
      INNER JOIN $wpdb->posts ON $wpdb->posts.ID = object_id
      WHERE DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= $wpdb->posts.post_date");

if($term_ids > 0){

  $tags = get_tags(array(
    'orderby' => 'count',
    'order'   => 'DESC',
    'number'  => 28,
    'include' => $term_ids,
  ));

  // your foreach loop here...

}else{
  print 'no posts were tagged in the last 30 days';

}

    There are probably more efficient ways to do this though.

  4. I think the get_tags->count does not really count the tags in a range. I have implemented this solution, please let me know if this works for you:

    global $wpdb;
$term_ids = $wpdb->get_col("
    SELECT term_id , count(*) cont FROM $wpdb->term_taxonomy
    INNER JOIN $wpdb->term_relationships ON $wpdb->term_taxonomy.term_taxonomy_id=$wpdb->term_relationships.term_taxonomy_id
    INNER JOIN $wpdb->posts ON $wpdb->posts.ID = $wpdb->term_relationships.object_id
    WHERE DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= $wpdb->posts.post_date AND  $wpdb->term_taxonomy.taxonomy='post_tag'
    GROUP BY term_id
    ORDER BY cont DESC
    LIMIT 5
");    
if ( count( $term_ids ) > 0 ) {
    $tags = get_tags( array(
        'orderby' => 'count',
        'order'   => 'DESC',
        'number'  => 5,
        'include' => $term_ids,
    ) );
    foreach ( (array) $tags as $tag ) {
        echo '<li><a href="' . get_tag_link ($tag->term_id) . '" rel="tag">' . 
            $tag->name . '</a></li>';
    }
}

  5. It worked, But I think performance isn’t good.

    global $wpdb;

$limit = 10;

$sql      = "SELECT ID FROM $wpdb->posts WHERE $wpdb->posts.post_type = 'digi_posts' AND $wpdb->posts.post_status = 'publish' AND DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= $wpdb->posts.post_date";
            $sql      = "SELECT term_taxonomy_id FROM $wpdb->term_relationships WHERE object_id in ($sql) GROUP BY term_taxonomy_id ORDER BY COUNT(term_taxonomy_id) DESC";
            $sql      = "SELECT term_id FROM $wpdb->term_taxonomy WHERE taxonomy = 'post_tag' AND term_taxonomy_id in ($sql) LIMIT {$limit}";
            $term_ids = $wpdb->get_col( $sql );

if ( count( $term_ids ) > 0 ) {
    $tags = get_tags( array(
        'orderby' => 'count',
        'order'   => 'DESC',
        'number'  => $limit,
        'include' => $term_ids,
    ) );
    foreach ( (array) $tags as $tag ) {
        echo '<li><a href="' . get_tag_link ($tag->term_id) . '" rel="tag">' . 
            $tag->name . '</a></li>';
    }
}