Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

I have 7 posts, like this:

1
2
3
4 - this is the current post
5
6
7

As noted, number four is the current post being displayed. I need to create a query that will allow me to display the previous 3 posts (by publication date) and also the three posts after. This can be done with two separate queries.

I've been able to display the immediately previous or next post, just not the ones further down / up.

Any ideas?

share|improve this question
    
What data do you want on these previous and next posts? Just the title, the content to, the permalink? –  t31os Apr 12 '11 at 9:26

4 Answers 4

up vote 3 down vote accepted

This can be done in a single query, though i can't speak specifically about how well this query will perform(i've not spent a great deal with Union queries - never had a need, till now)..

First, a function to select two sets of results, but using union to return them as a single result set.

function get_post_siblings( $limit = 3, $date = '' ) {
    global $wpdb, $post;

    if( empty( $date ) )
        $date = $post->post_date;

    //$date = '2009-06-20 12:00:00'; // test data

    $limit = absint( $limit );
    if( !$limit )
        return;

    $p = $wpdb->get_results( "
    (
        SELECT 
            p1.post_title, 
            p1.post_date,
            p1.ID
        FROM 
            $wpdb->posts p1 
        WHERE 
            p1.post_date < '$date' AND 
            p1.post_type = 'post' AND 
            p1.post_status = 'publish' 
        ORDER by 
            p1.post_date DESC
        LIMIT 
            $limit
    )
    UNION 
    (
        SELECT 
            p2.post_title, 
            p2.post_date,
            p2.ID 
        FROM 
            $wpdb->posts p2 
        WHERE 
            p2.post_date > '$date' AND 
            p2.post_type = 'post' AND 
            p2.post_status = 'publish' 
        ORDER by
            p2.post_date ASC
        LIMIT 
            $limit
    ) 
    ORDER by post_date ASC
    " );
    $i = 0;
    $adjacents = array();
    for( $c = count($p); $i < $c; $i++ )
        if( $i < $limit )
            $adjacents['prev'][] = $p[$i];
        else
            $adjacents['next'][] = $p[$i];

    return $adjacents;
}

There's a test date in there, you can safely ignore that or add in your own value for testing.

Here's some sample code you can use in your single.php loop to list out the results, though note this is just a generic example, and the function might need to select more/different data, but based on the info you've provided i wasn't sure exactly what you wanted, so the following is for illustration and to give a sample you can use to test the results..

<?php 
$siblings = get_post_siblings( 3 ); // This is the same as doing the call below(which is just for illustration)
//$siblings = get_post_siblings( 3, $post->post_date );

$prev = $siblings['prev'];

foreach( $prev as $p )
    echo get_the_time( 'd m Y', $p ) . ': ' . apply_filters( 'the_title', $p->post_title ) . '<br />';

$next = $siblings['next'];

foreach( $next as $p )
    echo get_the_time( 'd m Y', $p ) . ': ' . apply_filters( 'the_title', $p->post_title ) . '<br />';
?>

Awaiting feedback... :)

share|improve this answer
    
Brilliant. Worked perfectly. –  Pippin Apr 13 '11 at 21:09
    
Happy to help, let me know if you need help making adjustments.. :) –  t31os Apr 15 '11 at 11:22
    
Would you be able to tell me how to modify this to pull posts only from a specified category ID? –  Pippin Apr 18 '11 at 20:11
    
You'd need to add a couple of joins to each select query, i'll look into adding that when i get time to test, quite busy with other things right now. If you're desperate, have a google around, i know there have been numerous SQL examples that include joins to the taxonomy tables(i've even given examples in the past). –  t31os Apr 23 '11 at 7:52

3 x get_adjacent_post():

global $post;
$current_post = $post; // remember the current post

for($i = 1; $i <= 3; $i++){
  $post = get_previous_post(); // this uses $post->ID
  setup_postdata($post);

  // do your stuff here       
  the_title();

}

$post = $current_post; // restore

same for next 3 posts, just change the function to get_next_post()...


To do this with a single query, while still using the WP API, try changing the LIMIT value to 3 within the get_previous_post_sort and get_next_post_sort filters.

share|improve this answer
    
This is also expensive, it queries one by one. Best to copy get_adjacent_post and modify it to accept a post count - it's a simple function. –  Jan Fabry Apr 11 '11 at 20:25
    
6 small queries is not really a big deal... –  onetrickpony Apr 11 '11 at 23:20
    
I didn't try this, but it looks like it should work fine. –  Pippin Apr 13 '11 at 21:10
    
@onetrickpony yes, it's 600% of the original, quite a deal. –  Sam Aug 27 '13 at 11:13
    
@Sam: yes, but it's 600% of something very small –  onetrickpony Aug 27 '13 at 11:32

I don't think you can do this with a straight-up WP_Query(). Off the top of my head, I would probably:

  • make a new query
  • ask for all the posts you're looking for (by category/tag/etc.)
  • dump them into an array
  • find your post by its ID, and then
  • grab the surrounding posts if they exist

Here's an example of how this could work:

$findID = get_the_ID();

$n = new WP_Query( array( 'posts_per_page=-1' ) );

$postsArray = array();

if ( $n->have_posts() ) while ( $n->have_posts() ) : $n->the_post();
    $postsArray[] = get_the_ID();
endwhile;

$n = null;

if ( in_array( $findID, $postsArray ) )
{
    $prevItems = array();
    $nextItems = array();

    $pos = array_search( $findID, $postsArray );

    if ( array_key_exists( $pos - 3, $postsArray ) )
        $prevItems[0] = $postsArray[$pos - 3];

    if ( array_key_exists( $pos - 2, $postsArray ) )
        $prevItems[1] = $postsArray[$pos - 2];

    if ( array_key_exists( $pos - 1, $postsArray ) )
        $prevItems[2] = $postsArray[$pos - 1];

    if ( array_key_exists( $pos + 1, $postsArray ) )
        $nextItems[0] = $postsArray[$pos + 1];

    if ( array_key_exists( $pos + 2, $postsArray ) )
        $nextItems[1] = $postsArray[$pos + 2];

    if ( array_key_exists( $pos + 3, $postsArray ) )
        $nextItems[2] = $postsArray[$pos + 3];
}

This creates both a $prevItems and $nextItems array, if there are posts which fit that bill. The arrays are filled with the IDs of the posts.

share|improve this answer
    
looks very expensive. won't posts_per_page=-1 force a sql query for all existing posts? –  onetrickpony Apr 11 '11 at 19:27
    
Yeah, it is pretty expensive -- although my assumption is that the user would modify the query to only search for a single category/tag. I'm sure it's not optimal, but I don't know how else to do it. –  Gavin Anderegg Apr 11 '11 at 23:14

As JanFabry suggests in an answer to @onetrickpony above, you can modify get_adjacent_post(). This is what I did. Here's the function. I changed the function signature because it made more sense to me this way.

/**
 * Retrieve multiple adjacent posts. Adapted from get_adjacent_post()
 *
 * Can either be next or previous post.
 *
 * @since 2.5.0
 *
 * @param int       $post_id    Optional. Will fall back to loop.
 * @param int       $limit      Optional. Number of posts to return.
 * @param bool          $previous       Optional. Whether to retrieve previous or next posts.
 * @param bool          $in_same_term   Optional. Whether post should be in a same taxonomy term.
 * @param array|string  $excluded_terms Optional. Array or comma-separated list of excluded term IDs.
 * @param string        $taxonomy       Optional. Taxonomy, if $in_same_term is true. Default 'category'.
 * @return mixed        Array of post objects if successful. Null if global $post is not set. Empty string if no corresponding post exists.
 */
function pst_get_adjacent_posts( $post_id = null, $limit = 1, $previous = true, $in_same_term = false, $excluded_terms = '', $taxonomy = 'category' ) {
    global $wpdb;

    if ( ( ! $post = get_post( $post_id ) ) || ! taxonomy_exists( $taxonomy ) )
        return null;

    $current_post_date = $post->post_date;

    $join = '';
    $posts_in_ex_terms_sql = '';
    if ( $in_same_term || ! empty( $excluded_terms ) ) {
        $join = " INNER JOIN $wpdb->term_relationships AS tr ON p.ID = tr.object_id INNER JOIN $wpdb->term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id";

        if ( $in_same_term ) {
            if ( ! is_object_in_taxonomy( $post->post_type, $taxonomy ) )
                return '';
            $term_array = wp_get_object_terms( $post->ID, $taxonomy, array( 'fields' => 'ids' ) );
            if ( ! $term_array || is_wp_error( $term_array ) )
                return '';
            $join .= $wpdb->prepare( " AND tt.taxonomy = %s AND tt.term_id IN (" . implode( ',', array_map( 'intval', $term_array ) ) . ")", $taxonomy );
        }

        $posts_in_ex_terms_sql = $wpdb->prepare( "AND tt.taxonomy = %s", $taxonomy );
        if ( ! empty( $excluded_terms ) ) {
            if ( ! is_array( $excluded_terms ) ) {
                // back-compat, $excluded_terms used to be $excluded_terms with IDs separated by " and "
                if ( false !== strpos( $excluded_terms, ' and ' ) ) {
                    _deprecated_argument( __FUNCTION__, '3.3', sprintf( __( 'Use commas instead of %s to separate excluded terms.' ), "'and'" ) );
                    $excluded_terms = explode( ' and ', $excluded_terms );
                } else {
                    $excluded_terms = explode( ',', $excluded_terms );
                }
            }

            $excluded_terms = array_map( 'intval', $excluded_terms );

            if ( ! empty( $term_array ) ) {
                $excluded_terms = array_diff( $excluded_terms, $term_array );
                $posts_in_ex_terms_sql = '';
            }

            if ( ! empty( $excluded_terms ) ) {
                $posts_in_ex_terms_sql = $wpdb->prepare( " AND tt.taxonomy = %s AND tt.term_id NOT IN (" . implode( $excluded_terms, ',' ) . ')', $taxonomy );
            }
        }
    }

    $adjacent = $previous ? 'previous' : 'next';
    $op = $previous ? '<' : '>';
    $order = $previous ? 'DESC' : 'ASC';

    /**
     * Filter the JOIN clause in the SQL for an adjacent post query.
     *
     * The dynamic portion of the hook name, $adjacent, refers to the type
     * of adjacency, 'next' or 'previous'.
     *
     * @since 2.5.0
     *
     * @param string $join           The JOIN clause in the SQL.
     * @param bool   $in_same_term   Whether post should be in a same taxonomy term.
     * @param array  $excluded_terms Array of excluded term IDs.
     */
    $join  = apply_filters( "get_{$adjacent}_post_join", $join, $in_same_term, $excluded_terms );

    /**
     * Filter the WHERE clause in the SQL for an adjacent post query.
     *
     * The dynamic portion of the hook name, $adjacent, refers to the type
     * of adjacency, 'next' or 'previous'.
     *
     * @since 2.5.0
     *
     * @param string $where          The WHERE clause in the SQL.
     * @param bool   $in_same_term   Whether post should be in a same taxonomy term.
     * @param array  $excluded_terms Array of excluded term IDs.
     */
    $where = apply_filters( "get_{$adjacent}_post_where", $wpdb->prepare( "WHERE p.post_date $op %s AND p.post_type = %s AND p.post_status = 'publish' $posts_in_ex_terms_sql", $current_post_date, $post->post_type), $in_same_term, $excluded_terms );

    /**
     * Filter the ORDER BY clause in the SQL for an adjacent post query.
     *
     * The dynamic portion of the hook name, $adjacent, refers to the type
     * of adjacency, 'next' or 'previous'.
     *
     * @since 2.5.0
     *
     * @param string $order_by The ORDER BY clause in the SQL.
     */
    $sort  = apply_filters( "get_{$adjacent}_post_sort", "ORDER BY p.post_date $order LIMIT $limit" );

    $query = "SELECT p.ID FROM $wpdb->posts AS p $join $where $sort";
    $query_key = 'adjacent_post_' . md5( $query );
    $result = wp_cache_get( $query_key, 'counts' );
    if ( false !== $result ) {
        if ( $result )
            $result = array_map( 'get_post', $result );
        return $result;
    }

    $result = $wpdb->get_col( $query );
    if ( null === $result )
        $result = '';

    wp_cache_set( $query_key, $result, 'counts' );

    if ( $result )
        $result = array_map( 'get_post', $result );

    return $result;
}
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.