Multisite – List Recent Posts Across an Entire Network

In Multisite, List Recent Posts Across an Entire Network by using custom query or WP_Query you can list all recent post across the network.

This SQL query also has the potential to become very large. For this reason, we’ll use the Transients API again in a method very similar to what is used in the wp_list_sites() function.

Start by adding the wp_recent_across_network() function to your theme’s functions.php file.

1 . Using Transients API

/**
 * List recent posts across a Multisite network
 *
 * @uses get_blog_list(), get_blog_permalink()
 *
 * @param int $size The number of results to retrieve
 * @param int $expires Seconds until the transient cache expires
 * @return object Contains the blog_id, post_id, post_date and post_title
 */function wp_recent_across_network( $size = 10, $expires = 7200 ) {
    if( !is_multisite() ) return false;

    // Cache the results with the WordPress Transients API
    // Get any existing copy of our transient data
    if ( ( $recent_across_network = get_site_transient( 'recent_across_network' ) ) === false ) {

        // No transient found, regenerate the data and save a new transient
        // Prepare the SQL query with $wpdb
        global $wpdb;
        
        // Because the get_blog_list() function is currently flagged as deprecated
        // due to the potential for high consumption of resources, we'll use
        // $wpdb to roll out our own SQL query instead. Because the query can be
        // memory-intensive, we'll store the results using the Transients API
        if ( false === ( $site_list = get_site_transient( 'multisite_site_list' ) ) ) {
            $site_list = $wpdb->get_results( 'SELECT * FROM ' . $wpdb->prefix . 'blogs ORDER BY blog_id' );
            set_site_transient( 'multisite_site_list', $site_list, $expires );
        }
        
        $limit = absint($size);
        
        // Merge the wp_posts results from all Multisite websites into a single result with MySQL "UNION"
        foreach ( $site_list as $site ) {
            if( $site == $site_list[0] ) {
                $posts_table = $wpdb->prefix . "posts";
            } else {
                $posts_table = $wpdb->prefix . $site->blog_id . "_posts";
            }
            
            $posts_table = esc_sql( $posts_table );
            $blogs_table = esc_sql( $wpdb->prefix . 'blogs' );
            
            $query .= "(SELECT $posts_table.ID, $posts_table.post_title, $posts_table.post_date, $blogs_table.blog_id FROM $posts_table, $blogs_table";
            $query .= " WHERE $posts_table.post_type = 'post'";
            $query .= " AND $posts_table.post_status = 'publish'";
            $query .= " AND $blogs_table.blog_id = {$site->blog_id})";
            
            if( $site !== end($site_list) )
                $query .= " UNION ";
                else
                    $query .= " ORDER BY post_date DESC LIMIT 0, $limit";
        }
        
        // Sanitize and run the query
        $recent_across_network = $wpdb->get_results( $query );
        
        // Set the Transients cache to expire every two hours
        set_site_transient( 'recent_across_network', $recent_across_network, 60*60*2 );
    }
    // Format the HTML output
    if($recent_across_network) {
        $html = '<ul>';
        foreach ( $recent_across_network as $post ) {
            $html .= '<li><a href="'.get_blog_permalink($post->blog_id, $post->ID).'">'.$post->post_title.'</a></li>';
        }
        $html .= '</ul>';
        echo $html;
    }
}

2. Without Using Transients API

function wp_recent_across_network1( $size = 10) {
    if( !is_multisite() ) return false;

    // Prepare the SQL query with $wpdb
    global $wpdb;
    $site_list = $wpdb->get_results( 'SELECT * FROM ' . $wpdb->prefix . 'blogs ORDER BY blog_id' );

    $limit = absint($size);

    // Merge the wp_posts results from all Multisite websites into a single result with MySQL "UNION"
    foreach ( $site_list as $site ) {
        if( $site == $site_list[0] ) {
            $posts_table = $wpdb->prefix . "posts";
        } else {
            $posts_table = $wpdb->prefix . $site->blog_id . "_posts";
        }

        $posts_table = esc_sql( $posts_table );
        $blogs_table = esc_sql( $wpdb->prefix . 'blogs' );

        $query .= "(SELECT $posts_table.ID, $posts_table.post_title, $posts_table.post_date, $blogs_table.blog_id FROM $posts_table, $blogs_table";
        $query .= " WHERE $posts_table.post_type = 'post'";
        $query .= " AND $posts_table.post_status = 'publish'";
        $query .= " AND $blogs_table.blog_id = {$site->blog_id})";

        if( $site !== end($site_list) )
            $query .= " UNION ";
            else
                $query .= " ORDER BY post_date DESC LIMIT 0, $limit";
    }

    // Sanitize and run the query
    $recent_across_network = $wpdb->get_results( $query );

    // Format the HTML output
    if($recent_across_network) {
        $html = '<ul>';
        foreach ( $recent_across_network as $post ) {
            $html .= '<li><a href="'.get_blog_permalink($post->blog_id, $post->ID).'">'.$post->post_title.'</a></li>';
        }
        $html .= '</ul>';
        echo $html;
    }
}

Using this function in your theme is simple.

<?php
// Display recent posts across the entire network
wp_recent_across_network();
?>