Query Latest Posts on Multi-site Network WordPress
- SOLVED
I'm trying to pull in the latest posts from across a multi-site network using an advanced query. The code I'm using was originally posted over here: [[LINK href="http://wordpress.org/support/topic/aggregating-recent-posts-to-parent-site-in-multisite-install?replies=29"]]http://wordpress.org/support/topic/aggregating-recent-posts-to-parent-site-in-multisite-install?replies=29[[/LINK]]
Right now, my posts query is coming up empty. It detects that sites are present, but does not succeed in pulling up posts from the sites.
Here's the query code:
<?php
/*
Parameters
==========
$how_many (integer): how many recent posts are being displayed.
$how_long_days (integer): time frame to choose recent posts from (in days).
$how_many_words (integer): how many post's teaser are being displayed. Count by word. Default value are 50 words.
$remove_html (boolean): set true to remove any html tag within the post.
$sort_by (string - post_date/post_modified): You can short the lattest post by positing date (post_date) or posting update (post_modified).
Return
======
ID
post_url
post_title
post_content
author_url
author_name
post_date
post_time
comment_count
*/
function wpmu_latest_post($how_many = 10, $how_long_days = 30, $how_many_words = 50, $more_text = "[...]", $remove_html = true, $sort_by = 'post_date') {
global $wpdb;
//first, gat all blog id
$query = "SELECT blog_id FROM $wpdb->blogs WHERE blog_id !='1'";
$blogs = $wpdb->get_col($query);
if ($blogs) {
echo 'have blogs';
//we use blog id to loop post query
foreach ($blogs as $blog) {
$blogPostsTable = 'wp_'.$blog.'_posts';
$db_query = "SELECT $blogPostsTable.ID,
$blogPostsTable.post_author,
$blogPostsTable.post_title,
$blogPostsTable.guid,
$blogPostsTable.post_date,
$blogPostsTable.post_content,
$blogPostsTable.post_modified,
$blogPostsTable.comment_count
FROM $blogPostsTable WHERE $blogPostsTable.post_status = 'publish'
AND $blogPostsTable.post_date >= DATE_SUB(CURRENT_DATE(), INTERVAL $how_long_days DAY)
AND $blogPostsTable.post_type = 'post'";
$thispos = $wpdb->get_results($db_query);
foreach($thispos as $thispost) {
if($sort_by == 'post_date') {
$order = $thispost->post_date;
}
else{
$order = $thispost->post_modified;
}
$post_dates[] = $order;
$post_guids[$order] = $thispost->guid;
$blog_IDs[$order] = $blog;
$post_IDs[$order] = $thispost->ID;
$post_titles[$order] = $thispost->post_title;
$post_authors[$order] = $thispost->post_author;
$post_contents[$order] = $thispost->post_content;
$comments[$order] = $thispost->comment_count;
}
}
rsort($post_dates);
$union_results = array_unique($post_dates);
$ResultArray = array_slice($union_results, 0, $how_many);
foreach ($ResultArray as $date) {
$ID = $post_IDs[$date];
$id_author = $post_authors[$date];
$post_url = get_blog_permalink($blog_IDs[$date], $ID);/*$post_guids[$date];*/
$post_title = $post_titles[$date];
$post_content = $post_contents[$date];
$post_date = mysql2date(get_option('date_format'), $date);
$post_time = mysql2date(get_option('time_format'), $date);
$total_comment = $comments[$date];
$user_info = get_userdata($id_author);
$author_blog_url = get_blogaddress_by_id($user_info->primary_blog);
$author_url = $user_info->user_url;
$author_email = $user_info->user_email;
if($user_info->first_name) {
$author_name = $user_info->first_name.' '.$user_info->last_name;
}
else{
$author_name = $user_info->nickname;
}
if($remove_html) {
$post_content = wpmu_cleanup_post($post_content);
}
$results = array();
$results['ID'] = $ID;
$results['post_url'] = $post_url;
$results['post_title'] = $post_title;
$results['post_content'] = wpmu_cut_article_by_words($post_content, $how_many_words);
if ($results['post_content'] != $post_content)
$results['post_content'] .= sprintf(' <a href="%s">%s</a>', $post_url, $more_text);
$results['author_blog_url'] = $author_blog_url;
$results['author_url'] = $author_url;
$results['author_email'] = $author_email;
$results['author_name'] = $author_name;
$results['post_date'] = $post_date;
$results['post_time'] = $post_time;
$results['comment_count'] = $total_comment;
$returns[] = $results;
}
$latest_posts = wpmu_bind_array_to_object($returns);
return $latest_posts;
}
}
function wpmu_bind_array_to_object($array) {
$return = new stdClass();
foreach ($array as $k => $v) {
if (is_array($v)) {
$return->$k = wpmu_bind_array_to_object($v);
}
else {
$return->$k = $v;
}
}
return $return;
}
function wpmu_cut_article_by_words($original_text, $how_many) {
$word_cut = strtok($original_text," ");
$return = '';
for ($i=1;$i<=$how_many;$i++) {
$return .= $word_cut;
$return .= (" ");
$word_cut = strtok(" ");
}
$return .= '';
return $return;
}
function wpmu_cleanup_post($source) {
$replace_all_html = strip_tags($source);
$bbc_tag = array('/\[caption(.*?)]\[\/caption\]/is');
$result = preg_replace($bbc_tag, '', $replace_all_html);
return $result;
}
?>
And the function calling the query code:
<?php wpmu_latest_post(); ?>
Ideas are much appreciated :)
Answers (2)
John Cotton answers:
Hi Pippin
I've not been through your code in detail (so apologies if this doesn't help directly), but I see that you code does multiple wpdb queries against each table.
I use a a different approach - UNION ALL on a series of concatenated SQL statements. here's some code that works for me in a current project:
$blogs = $wpdb->get_results("SELECT blog_id FROM {$wpdb->blogs} WHERE blog_id > 1");
if( $blogs ) {
$sql = array();
foreach($blogs as $blog) {
$sql[] = "SELECT $blog->blog_id AS BlogID, p.ID, p.post_title, u.ID AS authorID, u.display_name FROM wp_mu_{$blog->blog_id}_posts p INNER JOIN wp_mu_users u ON p.post_author = u.id WHERE p.post_status = 'publish';
}
$sql = 'SELECT * FROM ('. implode(' UNION ALL ', $sql).') T1 LIMIT 50';
$posts = $wpdb->get_results($sql);
if($posts) {
// etc..
}
}
NB For clarity, I've removed a little bit from the SQL that gets me precisely what I want. Clearly you could add record count limits to each single query prior to the UNION ALL.
Equally, you could alter each individual SQL statement to vary what comes back from each blog table (maintaining the same columns results of course).
I find this approach has the advantage of simplicity (your query can be whatever you need) and performance (just the one get_results call).
John
PS Another NB: the " WHERE blog_id > 1" in the blogs query is to exclude the "master" blog something you may or may not need to do.
Pippin Williamson comments:
Thanks John. I've gotten some of it worked out, though your solution definitely might be better.
I'm going to wait and see what other answers roll in.
Thanks
Pippin Williamson comments:
Alright, let's see if you can help out with this.
I am able to return and display parts of the posts from the network, such as title, url, and content. But I haven't been able to get thumbnails or tags to display correctly.
Right now, using my original query code, I can display the URL like this:
echo $results['post_url']
and content like this:
echo $results['post_content'];
But doing this fails:
$posttags = get_the_tags($results['ID']);
if ($posttags) {
foreach($posttags as $tag) {
echo '<span><a href="' . get_tag_link($tag->term_id) . '">' . $tag->name . '</a></span>, ';
}
}
as does this:
echo get_the_post_thumbnail($results['ID'], 'post-image');
Ideas?
John Cotton comments:
Well, any standard function is going to only work on the current blog since the wpdb class has that set as a variable ($wpdb->blogid). So you're going to have to do the leg work yourself. That means retrieving the blog id in the original query and then construction your own methods to do things.
However - AND I'VE NOT TRIED THIS - if you change $wpdb->blogid to the require blog BEFORE you use the inbuilt functions, I can't think of reason why they wouldn't work (NB It might mean changing more than just $blogid, eg $prefix).
So something like:
$wpdb->blogid = $results['blogID']; // you'd need to add this column to your results set but if you use my approach that's easy enough
// This might NOT be correct or even necessary.....I'd try without first
$wpdb->prefix = $wpdb->base_prefix . '_' . $wpdb->blogid .'_';
$posttags = get_the_tags($results['ID']);
if ($posttags) {
foreach($posttags as $tag) {
echo '<span><a href="' . get_tag_link($tag->term_id) . '">' . $tag->name . '</a></span>, ';
}
}
Probably worth ago before you start rolling your own.
JC
Sébastien | French WordpressDesigner answers:
echo get_the_post_thumbnail($results['ID'], 'post-image');
it seems that this function can display an image which is in the blog and not an image which s in another blog
Pippin Williamson comments:
yes, that's what I've found.