(MYSQL) Get tags taxonomy by recent posts in WordPress DB

I’m developing a simple “trending hashtags”, which based on tags were used by recent posts. The result will be:

pidtags
postid2tag1,tag2,tag3
postid1tag1,tag5,tag6
GET TAGS BY RECENT POSTS

Here is the MySQL:

<?php
global $wpdb;
$results = $wpdb->get_results("SELECT `{$wpdb->prefix}term_relationships`.`object_id` AS pid,
GROUP_CONCAT(`{$wpdb->prefix}term_relationships`.`term_taxonomy_id`) AS tags 
FROM `{$wpdb->prefix}term_relationships` 
JOIN {$wpdb->prefix}term_taxonomy 
ON (`{$wpdb->prefix}term_relationships`.`term_taxonomy_id` = `{$wpdb->prefix}term_taxonomy`.`term_id` AND {$wpdb->prefix}term_taxonomy.taxonomy='post_tag') 
WHERE `object_id` IN (SELECT `ID` FROM `{$wpdb->prefix}posts` WHERE `post_type` = 'post' ORDER BY `{$wpdb->prefix}posts`.`post_date` DESC) 
GROUP BY `object_id` 
ORDER BY `{$wpdb->prefix}term_relationships`.`object_id` DESC LIMIT ", OBJECT );

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>