find a random blogid across my multisite network that has at least one post published

2 Views

I need to find a random blog across my multisite network that has at least one post published of a certain post_type, and return its blogid as a variable so I can use it in a switch_to_blog() call.

I need to figure out how to build the $randomblog variable in this scenario (I know the current SQL query is bogus):

Read More
// Find a random blog that has at least one post of post_type published and return its blogid as $randomblog
$randomblog = $wpdb->get_results($wpdb->prepare("SELECT (1 random blogid) FROM $wpdb->blogs WHERE at least 1 of post_type = 'special' exists "));

// Switch to the random blog
    switch_to_blog($randomblog);

        (do some fun stuff)

    // Switch back to the current blog
    restore_current_blog();

I’m not sure, but I believe this bit of code I found could possibly be altered to do what I need. Just not sure how: 

global $wpdb;
global $table_prefix;

// get an array of the table names that our posts will be in
// we do this by first getting all of our blog ids and then forming the name of the 
// table and putting it into an array
$rows = $wpdb->get_results( "SELECT blog_id from $wpdb->blogs WHERE
    public = '1' AND archived = '0' AND mature = '0' AND spam = '0' AND deleted = '0';" );
if ( $rows ) :
$blogPostTableNames = array();
foreach ( $rows as $row ) :
    $blogPostTableNames[$row->blog_id] = $wpdb->get_blog_prefix( $row->blog_id ) . 'posts';
endforeach;

// now we need to do a query to get all the posts from all our blogs
// ordered by the number of comments and with limits applied
if ( count( $blogPostTableNames ) > 0 ) :
    $query = '';
    $i = 0;
    foreach ( $blogPostTableNames as $blogId => $tableName ) :
        if ( $i > 0 ) :
            $query.= ' UNION ';
        endif;
        $query.= " SELECT ID, post_type, $blogId as `blog_id` FROM $tableName WHERE post_type = 'prompt' AND post_status = 'publish' ";
        $i++;
    endforeach;

    $rows = $wpdb->get_results( $query );

    // now we need to get each of our posts into an array and return them
    if ( $rows ) :
        $posts = array();
        foreach ( $rows as $row ) :
            $posts[] = get_blog_post( $row->blog_id, $row->ID );
        endforeach;
        print_r($posts);
        return $posts;
        echo ('<br /><br />');
    endif;
endif;
endif;
Post Views: 2

Related posts

Leave a Reply

1 comment

  1. An example that displays an admin notice listing a randomized array with all blog IDs, the result of a get_posts( array( 'numberposts' => 1 ) ) and marking the first one which get_posts result is different from zero.

    Result

    admin notice listing all blogs and number of posts found

    After refreshing:
    admin notice after refreshing

    Code

    add_action( 'admin_notices', 'wpse_60401_print_random_blog' );

function wpse_60401_print_random_blog()
{ 
    global $wpdb;

    $rows = $wpdb->get_results( $wpdb->prepare( "SELECT blog_id from $wpdb->blogs WHERE public = '1' AND archived = '0' AND mature = '0' AND spam = '0' AND deleted = '0';" ) );

    if( !$rows )
    {
        echo '<div class="error">No blogs found (!)</div>';
    }
    else
    {
        shuffle( $rows );
        $counter = 0;
        echo '<div class="error">';
        foreach ( $rows as $row ) 
        {
            switch_to_blog( $row->blog_id );
            $get_posts = get_posts( array( 'numberposts' => -1 ) );
            echo 'Blog ID: ' . $row->blog_id . ' - Number posts: ' . count($get_posts) . '<br />';

            if( count($get_posts) != 0 && $counter == 0 ) 
            {
                echo 'First blog with a post: ' . $row->blog_id . '<br />';
                $counter++;
            }
                    restore_current_blog();
        }
        echo '</div>';
    }

}

    Code in form an usable function

    function get_random_blog()
{ 
    global $wpdb;

    $rows = $wpdb->get_results( $wpdb->prepare( "SELECT blog_id from $wpdb->blogs WHERE public = '1' AND archived = '0' AND mature = '0' AND spam = '0' AND deleted = '0';" ) );

    if( !$rows )
    {
        return 0;
    }
    else
    {
        shuffle( $rows );
        foreach ( $rows as $row ) 
        {
            switch_to_blog( $row->blog_id );
            $get_posts = get_posts( array( 'numberposts' => -1 ) );

            if( count($get_posts) != 0 ) 
            {
                restore_current_blog();
                return $row->blog_id;               
            }
        }
    }
    restore_current_blog();
    return 0;
}