11

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

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

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

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' );  
  • Maybe it lists the revisions too? – passatgt Aug 24 '13 at 11:49
  • I thought I was only looking at published, because I removed pending, private, draft & future. Didn't notice a revision type. – jnthnclrk Aug 24 '13 at 11:51
  • Hmmm, doesn't appear to be a "revisions" status: codex.wordpress.org/Post_Status – jnthnclrk Aug 24 '13 at 11:55
  • try to print_r the post type or the post id in one of the columns, i think revisions are post types, so if you can see revisions, you have that also in the results. But i can also see that you only display results from the listings post type, so i think i'm wrong. But its worth a try:) – passatgt Aug 24 '13 at 11:57
  • Added a new grab with post statuses, post types and post ancestors. – jnthnclrk Aug 25 '13 at 12:30
11

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;
}
|improve this answer|||||
4

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
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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