Find a random blogid that has at least one post published in multisite

  • bullemhead

    (@bullemhead)


    11 years, 8 months ago

    I’m working on a weird little wordpress hack, and I’ve run up against the limits of my mysql/php knowledge.

    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:

    // Find a random blog that has at least one post of post_type published and return its blogid as $randomblog
    (some kind of query across multisite network)
// Switch to the random blog
		switch_to_blog($randomblog);

			(do some stuff)

		// Switch back to the current blog
		restore_current_blog();
Viewing 4 replies - 1 through 4 (of 4 total)
  • Moderator Ipstenu (Mika Epstein)

    (@ipstenu)

    🏳️‍🌈 Advisor and Activist

    11 years, 8 months ago

    The problem is you have to switch to blog to run the get posts… Unless you want to do it all via custom SQL.

    Thread Starter bullemhead

    (@bullemhead)

    11 years, 8 months ago

    Yeah it would have to be custom SQL because I can’t switch to a blog before knowing which one to switch to.

    Thread Starter bullemhead

    (@bullemhead)

    11 years, 8 months ago

    So I guess what I’m looking for is the appropriate SQL query to run so I can know which blog to switch to.

    Thread Starter bullemhead

    (@bullemhead)

    11 years, 8 months ago

    Here is some example code that finds posts across the network based on number of comments. I think it could be altered, but I’m 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 <code>blog_id</code> 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;
Viewing 4 replies - 1 through 4 (of 4 total)
  • The topic ‘Find a random blogid that has at least one post published in multisite’ is closed to new replies.