How to search for posts IN OR title OR content OR author?

by

I have looked at many posts and questions regarding searches for custom meta, but I want to search for posts and include those if the search query matches an author name.

I have come up with the following code but it is not working. For some reason, it returns posts with status “revision”

First, I hooked my three functions to their respective hooks:

add_filter('posts_join', 'custom_posts_join' );
add_filter('posts_groupby', 'custom_posts_groupby' );
add_filter('posts_where', 'custom_posts_where' );

Then, I proceed with each function:

custom_posts_join

function custom_posts_join($join){
    global $wpdb;
    if( is_search() && !is_admin()) {

        $join .= " LEFT JOIN $wpdb->postmeta ON $wpdb->posts.ID = $wpdb->postmeta.post_id ";
    }
    return $join;
}

custom_posts_groupby

function custom_posts_groupby( $groupby ) {
    global $wpdb;
    if( is_search() && !is_admin()) {

        $groupby = "$wpdb->posts.ID";
    }
    return $groupby;
}

custom_posts_where

function custom_posts_where( $where="" ){
    if ( is_search() && !is_admin()) {
        global $wpdb;

        $search = ( isset($_GET["s"]) ) ? sanitize_text_field($_GET["s"]) : false ;
        $search = (string)$search;

        $users = $wpdb->get_results( "SELECT user_id FROM $wpdb->usermeta WHERE meta_key = 'first_name' AND meta_value="$search"",OBJECT_K  );

        foreach ($users as $user)
        {
            $users_ids.=$user->user_id.','; 
        }
        $users_ids = rtrim($users_ids, ',');

        $where .= " AND ( ($wpdb->posts.post_author IN ({$users_ids})) 
               OR ($wpdb->posts.post_title LIKE '$search')
               OR ($wpdb->posts.post_content LIKE '$search') )
            AND $wpdb->posts.post_type="post"
            AND $wpdb->posts.post_status="publish"";  

        }
    return $where;
}

1 Answer
1

After some hard work and research, I finally came up with this working solution.

Please feel free to improve on this code and post an alternative answer. For some reason, the query seems to be running more than once (three to four times in my case).

Here, I am making use of three hooks, posts_search, posts_join and posts_groupby

Function custom_search_posts allows you to search for terms and users together.

-Search for users and terms. For example: If there is a post titled Money written by user Smith.
You can search for Smith money and the result will be said post. NOTE: author name has to come first than term.

-Search only for users (usermeta, first_name OR last_name)

-Search only for terms (in titles only).

-You can even search for several users at a time.

function custom_search_posts( $search, &$wp_query )
{
    global $wpdb;
    if ( empty( $search ) )
        return $search; // exit if search is empty

    $q = $wp_query->query_vars;
    $n = ! empty( $q['exact'] ) ? '' : '%';
    $search="";
    $searchand = '';

    add_filter('posts_join', 'custom_posts_join' );
    add_filter('posts_groupby', 'custom_posts_groupby' );   
    foreach ( (array) $q['search_terms'] as $term ) {
      $user_args = array( 
       'meta_query' => array(
        'relation' => 'OR',
          array(
            'key'     => 'first_name',
            'value'   => $term,
            'compare' => 'LIKE'
        ),
          array(
            'key'     => 'last_name',
            'value'   => $term,
            'compare' => 'LIKE'
        )
        ));
        $user_query = new WP_User_Query( $user_args );
        $users = $user_query ->get_results();
        $term = esc_sql( like_escape( $term ) );
        $search .= "{$searchand} $wpdb->posts.post_title LIKE '{$n}{$term}{$n}' ";
        if (!empty($users)) {
            foreach ($users as $user) {   
                $user_id = $user->ID;    
                $search .= " OR $wpdb->posts.post_author IN ('{$user_id}') ";           
            }                   
        }       

        $searchand = ' AND ';
    }
    if ( ! empty( $search ) ) {
        $search = " AND ({$search}) ";
        if ( ! is_user_logged_in() )
            $search .= " AND ($wpdb->posts.post_password = '') ";
    }

    $search .= " AND $wpdb->posts.post_type IN ('post')";
    $search .= " AND $wpdb->posts.post_status="publish"";

    remove_filter('posts_join', 'custom_posts_join' );
    remove_filter('posts_groupby', 'custom_posts_groupby' );        
    return $search;
}
add_filter( 'posts_search', 'custom_search_posts', 500, 2 );

Function custom_posts_join allows us to join postmeta on posts to search posts by author.

function custom_posts_join($join){
    global $wpdb;
    if( is_search() && !is_admin()) {
        $join .= " LEFT JOIN $wpdb->postmeta ON $wpdb->posts.ID = $wpdb->postmeta.post_id ";    
    }
    return $join;
}

Function custom_posts_groupby now we group postmeta and posts by posts ID

function custom_posts_groupby( $groupby ) {
    global $wpdb;
    if( is_search() && !is_admin()) {   
        $groupby = "$wpdb->posts.ID";
    }
    return $groupby;
}

I had some trouble querying users through mysql queries so I used WP_User_Query instead.

I hope someone comes and improves on the code, mostly performance wise.

EDIT:
There is no need for posts_join and posts_groupby in this case.

Leave a Comment