Support » Networking WordPress » Find a random blogid that has at least one post published in multisite

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

    Lead Plugin Wrangler

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

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

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

    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.