1

I have the following custom search active in our WP admin for only administrators

add_action('pre_get_posts', 'query_custom_admin_search', 21  );
function query_custom_admin_search( $wp_query ) {
    global $current_user;

    if( is_admin() ) {
        if (get_current_user_role()=="administrator"){
            if ( $wp_query->query['post_type'] != "apartments" ){
                return;
            }else{
                $custom_fields = array("city","state_county",);
                $search_term = $wp_query->query_vars['s'];
                
                if ( $search_term != '' ) {
                    $meta_query = array( 'relation' => 'OR' );
                    foreach( $custom_fields as $custom_field ) {
                        array_push( $meta_query, array(
                            'key' => $custom_field,
                            'value' => $search_term,
                            'compare' => 'LIKE'
                        ));
                    }
                    $wp_query->set( 'meta_query', $meta_query );
                };

                return;
            }
        }else{
            return;
        }
    }else{
        return;
    }
}

This searches custom meta keys that we need to check and it works great. The only problem is that the default search columns in the wp_posts table such as post_title seem to no longer be searched.

How can I do both in this query ?

3
  • 1
    I remembered having answered a similar question in the past, and the solution was to use a custom WHERE condition if I'm not mistaken. So the query would be like WHERE ( <meta query> OR <search query> ) - that's what you're trying to do, right?
    – Sally CJ
    Jan 19 at 4:04
  • 1
    And is there a specific reason you didn't create the city and state_county as custom taxonomies instead? Because taxonomy is the better approach for categorizing posts, or grouping posts together.
    – Sally CJ
    Jan 19 at 4:05
  • The custom fields I need were created by the Advanced Custom Fields plugin, that is why the search extends in this way. Do you have reference to that other question you answered ? Thanks!
    – AdamJones
    Jan 20 at 1:06

2 Answers 2

4
+50

The only problem is that the default search columns in the wp_posts table such as post_title seem to no longer be searched.

They're actually being searched, but the operator used is AND instead of OR as in WHERE ( <search query> AND <meta query> ), hence if for example one searched for foo, then WordPress will search for posts which..

  • Contain foo in the post title/content/excerpt and

  • Contain foo in the meta city or state_county

So if you want the operator be changed to OR, i.e. search in the post title/content/excerpt or your custom fields, then you can try the following which simply repositions the default search query in the WHERE clause:

  1. This snippet modifies the meta queries (to add your custom fields) and also sets a private arg used as a flag which if true, then we'll reposition the search query.

    add_action( 'pre_get_posts', 'query_custom_admin_search', 21 );
    function query_custom_admin_search( $query ) {
        // Check if the current user has the 'administrator' role.
        if ( ! in_array( 'administrator', (array) wp_get_current_user()->roles ) ) {
            return;
        }
    
        // Check if we're on the "Posts" page at wp-admin/edit.php?post_type=apartments
        // and that a search keyword is set.
        if ( ! is_admin() || ! $query->is_main_query()     ||
            'edit-apartments' !== get_current_screen()->id ||
            ! strlen( $query->get( 's' ) )
        ) {
            return;
        }
    
        // Retrieve existing meta queries, if any.
        $meta_query = (array) $query->get( 'meta_query' );
    
        // Define your custom fields (meta keys).
        $custom_fields = array( 'city', 'state_county' );
        // This is for your custom fields above.
        $meta_query2   = array( 'relation' => 'OR' );
    
        $s = $query->get( 's' );
        foreach ( $custom_fields as $meta_key ) {
            $meta_query2[] = array(
                'key'     => $meta_key,
                'value'   => $s,
                'compare' => 'LIKE',
            );
        }
    
        // Add your meta query to the existing ones in $meta_query.
        $meta_query[] = $meta_query2;
        $query->set( 'meta_query', $meta_query );
    
        // Set a custom flag for the posts_search and posts_where.
        $query->set( '_search_OR', true );
    }
    
  2. This snippet uses the posts_search hook to "empty" the search query, after storing it in a private arg which we'll use in the posts_where hook where we reposition the search query.

    add_filter( 'posts_search', 'wpse_401476_admin_posts_search', 21, 2 );
    function wpse_401476_admin_posts_search( $search, $query ) {
        if ( $query->get( '_search_OR' ) ) {
            $query->set( '_search_SQL', $search );
            $search = '';
        }
    
        return $search;
    }
    
  3. Now this snippet is the one that repositions the search query. It uses WP_Meta_Query::get_sql() to retrieve the meta query's (SQL) clauses (join and where) used in the current query.

    add_filter( 'posts_where', 'wpse_401476_admin_posts_where', 21, 2 );
    function wpse_401476_admin_posts_where( $where, $query ) {
        if ( $query->get( '_search_OR' ) &&
            $search = $query->get( '_search_SQL' )
        ) {
            global $wpdb;
    
            $clauses = $query->meta_query->get_sql( 'post', $wpdb->posts, 'ID', $query );
    
            if ( ! empty( $clauses['where'] ) ) {
                $where2 = "( 1 $search ) OR ( 1 {$clauses['where']} )";
                $where  = str_replace( $clauses['where'], " AND ( $where2 )", $where );
    
                $query->set( '_search_SQL', false );
                $query->set( '_search_OR', false );
            }
        }
    
        return $where;
    }
    

    So with the above, we'd get a condition that looks like ( ( 1 AND <search query> ) OR ( 1 AND <meta query> ) ) which is equivalent to the ( <search query> OR <meta query> ).

Tried & tested working on WordPress v5.8.3.

Notes:

  1. Use the above snippets instead of what you have in the question.

  2. The operator would only be changed if the current user has the administrator role and that the current page is the admin page for managing posts in the apartments post type, and that the search keyword is set, and I also checked if the current query/WP_Query is the main query.

  3. I used 21 as the priority, hence you might want to use a greater number (e.g. 100) to make the filters apply properly (i.e. not overwritten by plugins or the active theme).

4
  • Amazing Sally CJ thanks so much! This is a really good basis for what I need. This is a really good reference for others I hope.
    – AdamJones
    Jan 24 at 11:14
  • I'm glad I wasn't mistaken that you indeed wanted to change the AND to OR 🙂
    – Sally CJ
    Jan 25 at 0:35
  • However, the reason I initially asked why didn't you use custom taxonomies/terms instead, is because unlike taxonomies/terms, meta values are not indexed and thus meta queries like in your (and the above) code, which finds posts by specific meta values, can cause your site to run (extremely) slow when the site becomes large(r). So please consider using taxonomy queries instead.
    – Sally CJ
    Jan 25 at 0:36
  • The AND search, however, also applies even when doing taxonomy queries (i.e. WordPress would do WHERE <search query> AND <taxonomy query>), and unfortunately, changing the AND wouldn't be as simple as repositioning the search query.. (you would have to manually construct the taxonomy query's SQL).
    – Sally CJ
    Jan 25 at 0:42
0

try to return the $wp_query when is not admin

add_action('pre_get_posts', 'query_custom_admin_search', 21  );
function query_custom_admin_search( $wp_query ) {
    global $current_user;
    if (current_user_can('administrator' && is_admin()){
        if ( $wp_query->query['post_type'] != "apartments" ){
            return;
        }else{
            $custom_fields = array("city","state_county",);
            $search_term = $wp_query->query_vars['s'];
            if ( $search_term != '' ) {
                $meta_query = array( 'relation' => 'OR' );
                foreach( $custom_fields as $custom_field ) {
                    array_push( $meta_query, array(
                        'key' => $custom_field,
                        'value' => $search_term,
                        'compare' => 'LIKE'
                    ));
                }
                $wp_query->set( 'meta_query', $meta_query );
            }
            return;
        }
    }else{
        return $wp_query;
    }
}

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.