Display more recent and less recent posts in same category

The challenge:

Get n = quantity older and newer posts from the recent posts with the same categories and/or tags.

The solution:

Like @TwerkingN00b suggested, we do need some custom SQL here. The magic SQL phrase we are looking for is UNION.

UNION is used to combine the result from multiple SELECT statements into a single result set.

All the other stuff is knowledge how WordPress handles SQL queries, how to get the relevant data and then putting it all together. Let us start:

Setting up the basics. Our reference post (= current ID if nothing is supported), its date and some empty variables additionally we want to direct our query to ignore or accept the associated categories or terms.

function the_assoc_posts($post_id = 0, $quantity = 3, $ignore_categories = false, $ignore_tags = false) {
    if (empty($post_id)) $post_id = get_the_ID();
    if (empty($post_id)) return;

    $post_id = get_the_ID();
    $date = get_the_date( 'Y-m-d H:i:s', $post_id );
    $cat = array();
    $tag = array();

Now we need to get all the categories of our post.

    if (!$ignore_categories) {
        $post_categories = wp_get_post_terms( $post_id, 'category' );
        if (!empty($post_categories) && is_array($post_categories)) {
            foreach ($post_categories as $category) {
                $cat[] =  $category->term_id;
            }
            $cat = implode(',',$cat);
        }
    }

Now we need to get all the post_tags of our post.

    if (!$ignore_tags) {
        $post_tags = wp_get_post_terms( $post_id );
        if (!empty($post_tags) && is_array($post_tags)) {
            foreach ($post_tags as $post_tag) {
                $tag[] =  $post_tag->term_id;
            }
            $tag = implode(',', $tag);
        }
    }

Because WordPress doesn’t supports this kind of queries (yet) we have to rebuild the basic query and adapt it to our needs.

    global $wpdb, $post;

    $innerjoin = array();
    $where = array();

    if (!empty($cat)) {
        $innerjoin[]= "INNER JOIN $wpdb->term_relationships AS tt1 ON ($wpdb->posts.ID = tt1.object_id)";
        $where[] = "tt1.term_taxonomy_id IN ($cat)";
    }
    if (!empty($tag)) {
        $innerjoin[]= "INNER JOIN $wpdb->term_relationships AS tt2 ON ($wpdb->posts.ID = tt2.object_id)";
        $where[] = "tt2.term_taxonomy_id IN ($tag)";
    }
    $innerjoin = implode("\n", $innerjoin);
    if (!empty($where))
        $where="WHERE (".implode(" AND \n", $where).') AND';
    else
        $where = "WHERE";

    $query = "SELECT
      $wpdb->posts.ID,
      $wpdb->posts.post_date as DateOrder,
      2 SortOrder
    FROM $wpdb->posts
    {$innerjoin}
    {$where} $wpdb->posts.ID = {$post_id}
    UNION ALL
    (SELECT
      $wpdb->posts.ID,
      $wpdb->posts.post_date,
      3
    FROM $wpdb->posts
    {$innerjoin}
    {$where}
    $wpdb->posts.post_type="post"
    AND $wpdb->posts.post_status="publish"
    AND $wpdb->posts.post_date > '{$date}'
    GROUP BY $wpdb->posts.ID
    ORDER BY $wpdb->posts.post_date ASC LIMIT $quantity)
    UNION ALL
    (SELECT
      $wpdb->posts.ID,
      $wpdb->posts.post_date,
      1
    FROM $wpdb->posts
    {$innerjoin}
    {$where} $wpdb->posts.post_type="post"
    AND $wpdb->posts.post_status="publish"
    AND $wpdb->posts.post_date < '{$date}'
    GROUP BY $wpdb->posts.ID
    ORDER BY $wpdb->posts.post_date DESC LIMIT $quantity)
    ORDER BY SortOrder ASC, DateOrder DESC";

I found the basics on how to get rows before and after a particular row on stackoverflow and the sorting was also explained there. To find out how the current version of WordPress (v4.4) handles the query I built a custom query with tax_query parameters and echoed the SQL.

Now we need to loop through the found posts.

    $loop = $wpdb->get_results($query);

    if ( $loop ) {
        foreach ( $loop as $row ) {
            $post = get_post($row->ID);
            setup_postdata( $post );
            ?>
            <div class="three-titles">
                <a href="https://wordpress.stackexchange.com/questions/218112/<?php the_permalink() ?>"<?php echo (get_the_ID() == $post_id?' class="current"':'')?>><?php the_title(); ?></a></br>
            </div>
            <?php
        }
    }
    wp_reset_postdata();
}

And we got results. To get the associated posts:

• Inside the loop: the_assoc_posts();
• Outside the loop: the_assoc_posts($post_id);
• With max. of 5 associated posts: the_assoc_posts($post_id, 5);
• Ignore associated categories: the_assoc_posts(null, 3, false);
• Ignore associated tags: the_assoc_posts(null, 3, true, false);