Best Practice For Querying Grandchildren?

Question

Objective: Query and loop through a page's third-level "grandchildren" in a way that allows pagination.

Here's the code that I'm using (pagination excluded):

        // Get the ID of the first generation
        $gen1_ids = $post->ID;

        // Query for second generation IDs
        $gen2 = $wpdb->get_col( "SELECT ID FROM $wpdb->posts WHERE $wpdb->posts.post_parent IN ($gen1_ids) AND $wpdb->posts.post_type = 'page' AND $wpdb->posts.post_status = 'publish' ORDER BY $wpdb->posts.ID ASC" ); // Test ordering by title

        // Implode the results for further use
        $gen2_ids = implode( $gen2,', ' );

        // Now, query for third generation IDs using second generation IDs
        $gen3 = $wpdb->get_col( "SELECT ID FROM $wpdb->posts WHERE $wpdb->posts.post_parent IN ($gen2_ids) AND $wpdb->posts.post_type = 'page' AND $wpdb->posts.post_status = 'publish' ORDER BY $wpdb->posts.ID ASC" );

        $args = array(
          'post__in' => $gen3,
          'post_type' => 'page',
          'post_status' => 'publish',
          'posts_per_page' => 5,
          'paged' => get_query_var('paged')
        );

        $results = null;
        $results = new WP_Query( $args );

        if( $results->have_posts() ) { 
            while ( $results->have_posts() ) : $results->the_post(); ?>
                <li>
                    <a href="<?php the_permalink(); ?>" class="img"><img src="#"></a>
                    <div class="text">
                        <h3><a href="<?php the_permalink() ?>"><?php the_title(); ?></a></h3>
                        <p>...</p>
                    </div>
                </li>
        <?php endwhile;
        } /* end if */ ?>

     <?php wp_reset_query();  // Restore global post data stomped by the_post(). ?>

I've adapted the code from an answer to a support question two years back on the WordPress.org forums. The code is working.

Is there a way that this can be done better?

Answer 1

You're on the right track. You can do this with just one MySQL query instead of two by joining on the post_parent. Drop this function into your functions.php file, then in your template you can replace your WP_Query call with $results = wpse_84810_get_grandchildren();, change your if clause to be if( $results && $results->have_posts() ), then carry on with the rest of your template code.

function wpse_84810_get_grandchildren( $grandparent_id = false ) {
    global $wpdb;

    if ( !$grandparent_id )
        $grandparent_id = get_the_ID();

    $grandchildren_ids = $wpdb->get_col( $wpdb->prepare( "SELECT p1.ID FROM {$wpdb->posts} AS p1 INNER JOIN {$wpdb->posts} AS p2 ON p1.post_parent = p2.ID WHERE p2.post_parent = %d", $grandparent_id ) );

    if ( $grandchildren_ids ) {
        return new WP_Query( array(
            'post__in' => $grandchildren_ids,
            'post_type' => 'page',
            'post_status' => 'publish',
            'posts_per_page' => 5,
            'paged' => get_query_var( 'paged' ) ? get_query_var( 'paged' ) : 1
        ) );
    }

    return false;
}