SQL query to get adjacent posts from search query

Question

I'm busy rewritting a custom function I have recently written to get adjacent posts according to a tag, category, custom taxonomy term, author or search value saved in the URL

I'm using the structure of get_adjacent_post to rewrite my function.

Here is what I have. This successfully retrieves the ID of the adjacent post and returns it according to the query_vars set in the URL. If no custom query_vars are set, the function gets the adjacent post in normal chronological order as normal

<?php    
function get_adjacent_include_referred_post( $previous = true ) {
    global $wpdb;

    if ( ( ! $post = get_post() ) )
        return null;

    $current_post_date = $post->post_date;

    $join = '';
    $where = '';

    //Alter the SQL query if 'aq' is set in single page URL
    if( is_author_referrer() ) {

        $author = $_GET['aq'];
        $where .= $wpdb->prepare( "AND p.post_author IN (%s)", $author );

    }

    //Alter the SQL query if 'tq' is set in single page URL
    if( is_tax_referrer() ) {

        $term = $_GET['tq'];
        $join = $wpdb->prepare( " INNER JOIN $wpdb->term_relationships AS tr ON p.ID = tr.object_id  AND tr.term_taxonomy_id IN (%s)", $term );

    }

    //Alter the the SQL query if sq is set in single page URL
    if( is_search_referrer() ) {

        $term = $_GET['sq'];
        // **MISSING INFO** 
    }


    //Set next and previous post's operators to form the SQL query
    $adjacent   = $previous ? 'previous' : 'next';
    $op         = $previous ? '<' : '>';
    $order      = $previous ? 'DESC' : 'ASC';

    // Setting the default clauses to build the custom SQL query to get adjacent post
    $where = $wpdb->prepare( "WHERE p.post_date $op %s AND p.post_type = %s AND p.post_status = 'publish' $where", $current_post_date, $post->post_type );
    $sort  = "ORDER BY p.post_date $order LIMIT 1";
    $query = "SELECT p.ID FROM $wpdb->posts AS p $join $where $sort";

    //Create and set cache to improve performance of the function
    $query_key = 'get_the_adjacent_post_' . md5( $query );
    $result = wp_cache_get( $query_key, 'counts' );

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

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

    return $result;

}

My problem, I cannot get anything to work for a search query (my SQL knowledge is just to poor to assemble something useful). If my search query is rocks and someone clicks on a single post, how do I adjust my SQL query to get the adjacent post from the current single post being viewed within the same search query?

FEW NOTES ON MY CODE

• The is_***_referrer() conditional checks are wrapper functions which checks if the appropriate S_GET['***'] is set. For the search query, is_search_referrer() checks if $_GET['sq'] is set, returns true if the query_vars exists, false on failure

• The value of $_GET['sq'] will is the value of get_search_query(). This is the value that should be used to get the adjacent post for the same search query

• There is no syntax errors in my code and it works. I know the code looks funny for some reason in the code block above, and it does seem from that that there are syntax errors, but there aren't.

Any suggestions on what to add?

Answer 1

Although this is possible, it really is not viable. I've done a lot of research on this subject, but could not find a straight forward answer

I went and had a look at the WP_Query class for a possible solution, and I came to the conclusion that what I was trying to accomplish would not be possible with a simple one or two liner code.

PITFALLS

• To get the correct adjacent post, you have to match the original search query. Any deviation from the original search query will return incorrect adjacent posts

• Single search terms are quite easy to replicate and in most cases worked perfectly. The problem arises with multiple search terms or sentences. This is handled very specifically by the WP_Query class.

• There is a stopword filter in the WP_Query class which filters the search terms and remove these stopwords from the search query if they exist. So this would need to be replicated as well in a custom searh query for it to be consistent with the original search results

CONCLUSION

To get consistent and correct results, you have to copy the the complete search query section from the WP_Query class and incorporate it into the function in question. This is really not viable.

The best solution is to keep using WP_Query to keep the results consistent from the referrer pages and to get the correct adjacent posts. The method in the linked answer from the question is a bit bulky and redundant, and I'm busy rewriting it. Adjacent posts can be retrieved very easily by making use of the date_query parameters that was introduced in Wordpress version 3.7