Tag: mysql

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

pid	tags
postid2	tag1,tag2,tag3
postid1	tag1,tag5,tag6

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 );