How do I improve this admin query snippet to avoid generating duplicate results on non-meta searches?

Question

I've been playing around with code snippets which add meta data to admin searches.

The best snippet I've found was written by Stefano on this question.

However, it appears to have 1, annoying bug when searching non-meta terms.

Here are some grabs from my local dev install. I've printed the 2 MySQL queries onto the screen.

View of the single CPT post I'm using to test

This is the code working as expected and allowing me to search meta data from admin

Unfortunately the code creates duplicates on non-meta matches, in this case on post title

A grab showing the post status, post type and post ancestors of dupes

!A grab showing the post status, post type and post ancestors of dupes

Here is the code I'm running, it's basically the same as Stefano's, but with my crude attempts to make the query work.

/*
 * Search custom fields from admin keyword searches
 */

function rel_search_join( $join ) {
    global $pagenow, $wpdb;
    if ( is_admin() && $pagenow == 'edit.php' && $_GET['post_type'] == 'listings' && $_GET['s'] != '') {    
        $join .= 'LEFT JOIN ' . $wpdb->postmeta . ' ON '. $wpdb->posts . '.ID = ' . $wpdb->postmeta . '.post_id ';
    }
    echo '<br><strong>JOIN</strong>: ';
    print_r ( $join );
    echo '<br>';
    return $join;
}
add_filter('posts_join', 'rel_search_join' );

function rel_search_where( $where ) {
    global $pagenow, $wpdb;
    if ( is_admin() && $pagenow == 'edit.php' && $_GET['post_type']=='listings' && $_GET['s'] != '' ) {
        $where = preg_replace( "/\(\s*".$wpdb->posts.".post_title\s+LIKE\s*(\'[^\']+\')\s*\)/", "(".$wpdb->posts.".post_title LIKE $1) OR (".$wpdb->postmeta.".meta_value LIKE $1)", $where );
        $where = str_replace( "OR wp_posts.post_status = 'pending'", "", $where );
        $where = str_replace( "OR wp_posts.post_status = 'private'", "", $where );
        $where = str_replace( "OR wp_posts.post_status = 'draft'", "", $where );
        $where = str_replace( "OR wp_posts.post_status = 'future'", "", $where );
    }
    echo '<br><strong>WHERE</strong>: ';
    print_r ( $where );
    echo '<br>';
    return $where;
}
add_filter( 'posts_where', 'rel_search_where' );  

Answer 1

A GROUP BY statement can group your posts after the JOIN. For Wordpress you can use the posts_groupby filter.

add_filter( 'posts_groupby', 'my_post_limits' );
function my_post_limits($groupby) {
    global $pagenow, $wpdb;
    if ( is_admin() && $pagenow == 'edit.php' && $_GET['post_type']=='listings' && $_GET['s'] != '' ) {
        $groupby = "$wpdb->posts.ID";
    }
    return $groupby;
}

Answer 2

Thanks for your work on this, folks. This code got me most of the way there, but using WP 3.8 I was getting a SQL non-unique table/alias error, so I made some changes. For it to work on my setup I had to set a $wpdb->postmeta alias that was used in the JOIN statement. I also check only check once to see if the hooks should be used so they don't fire every time. Hope this helps someone!

global $postmeta_alias, $is_specials_search;
$cpt_name = 'special';
$postmeta_alias = 'pdpm'; // Change this to whatever your custom post type is
$is_specials_search = is_admin() && $pagenow=='edit.php' && isset( $_GET['post_type'] ) && $_GET['post_type']==$cpt_name && isset( $_GET['s'] );

// Extend search to include 'description' field
if ( $is_specials_search ) {
  add_filter( 'posts_join',      'pd_description_search_join' );
  add_filter( 'posts_where',     'pd_description_search_where' );
  add_filter( 'posts_groupby',   'pd_search_dupe_fix' );
}

function pd_description_search_join ($join){
  global $pagenow, $wpdb, $postmeta_alias, $is_specials_search;

  if ( $is_specials_search )  
    $join .='LEFT JOIN '.$wpdb->postmeta. ' as ' . $postmeta_alias . ' ON '. $wpdb->posts . '.ID = ' . $postmeta_alias . '.post_id ';

  return $join;
} // END search_join

function pd_description_search_where( $where ){
  global $pagenow, $wpdb, $postmeta_alias, $is_specials_search;

  if ( $is_specials_search )
    $where = preg_replace(
     "/\(\s*".$wpdb->posts.".post_title\s+LIKE\s*(\'[^\']+\')\s*\)/",
     "(".$wpdb->posts.".post_title LIKE $1) OR (".$postmeta_alias.".meta_value LIKE $1)", $where );

  return $where;
} // END search_where

function pd_search_dupe_fix($groupby) {
    global $pagenow, $wpdb, $is_specials_search;

    if ( $is_specials_search )
      $groupby = "$wpdb->posts.ID";

    return $groupby;
} // END pd_search_dupe_fix