WordPress.org

Ready to get started?Download WordPress

Forums

Find a random blogid that has at least one post published in multisite (5 posts)

  1. bullemhead
    Member
    Posted 2 years 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();
  2. The problem is you have to switch to blog to run the get posts... Unless you want to do it all via custom SQL.

  3. bullemhead
    Member
    Posted 2 years ago #

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

  4. bullemhead
    Member
    Posted 2 years 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.

  5. bullemhead
    Member
    Posted 2 years 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;

Topic Closed

This topic has been closed to new replies.

About this Topic