44

I need to do a WP_Query with a LIKE on the post_title.

I started with this regular WP_Query:

$wp_query = new WP_Query( 
    array (
        'post_type'        => 'wp_exposants',
        'posts_per_page'   => '1',
        'post_status'      => 'publish',
        'orderby'          => 'title', 
        'order'            => 'ASC',
        'paged'            => $paged
    )
); 

But what I actually want to do looks like this in SQL:

$query = "
        SELECT      *
        FROM        $wpdb->posts
        WHERE       $wpdb->posts.post_title LIKE '$param2%'
        AND         $wpdb->posts.post_type = 'wp_exposants'
        ORDER BY    $wpdb->posts.post_title
";
$wpdb->get_results($query);

The output prints the results i'm excpecting, but I use the regular <?php while ( $wp_query->have_posts() ) : $wp_query->the_post(); ?> to display the results.
And that is not working with $wpdb->get_results().

How can I achieve what I described here?

45

I would solve this with a filter on WP_Query. One that detects an extra query variable and uses that as the prefix of the title.

add_filter( 'posts_where', 'wpse18703_posts_where', 10, 2 );
function wpse18703_posts_where( $where, &$wp_query )
{
    global $wpdb;
    if ( $wpse18703_title = $wp_query->get( 'wpse18703_title' ) ) {
        $where .= ' AND ' . $wpdb->posts . '.post_title LIKE \'' . esc_sql( $wpdb->esc_like( $wpse18703_title ) ) . '%\'';
    }
    return $where;
}

This way you can still call WP_Query, you just pass the title as the wpse18703_title argument (or change the name to something shorter).

  • This one is somehow missing the $wpdb->prepare(). – kaiser Nov 6 '12 at 15:46
  • @kaiser: It's been a long time, but I think this was not possible with prepare(). $wpdb->prepare('LIKE "%s%%"', 'banana') would return "LIKE ''banana'%'", so we have to construct the query ourselves, and do the escaping too. – Jan Fabry Nov 6 '12 at 19:36
  • 1
    @JanFabry Happy to see you agaaaaaaaain! :) Drop by in chat some time, hm? StopPress would be happy to see you. About that prepare(). Yeah, that's tricky and I had to try that several times, before I got around it. From something I just made: $wpdb->prepare( ' AND {$wpdb->posts}.post_title LIKE %s ', esc_sql( '%'.like_escape( trim( $term ) ).'%' ) ). And I'm pretty sure the esc_sql() is unnecessary and just paranoid. – kaiser Nov 7 '12 at 10:35
  • It seems that you can't search a string with ' (apostrophe) inside. I guess it's because of escaping ? I didn't find the solution yet – Vincent Decaux Aug 30 '18 at 15:10
19

Simplified:

function title_filter( $where, &$wp_query )
{
    global $wpdb;
    if ( $search_term = $wp_query->get( 'search_prod_title' ) ) {
        $where .= ' AND ' . $wpdb->posts . '.post_title LIKE \'%' . esc_sql( like_escape( $search_term ) ) . '%\'';
    }
    return $where;
}

$args = array(
    'post_type' => 'product',
    'posts_per_page' => $page_size,
    'paged' => $page,
    'search_prod_title' => $search_term,
    'post_status' => 'publish',
    'orderby'     => 'title', 
    'order'       => 'ASC'
);

add_filter( 'posts_where', 'title_filter', 10, 2 );
$wp_query = new WP_Query($args);
remove_filter( 'posts_where', 'title_filter', 10, 2 );
return $wp_query;
  • 13
    Please include an explanation along with your code. – s_ha_dum Apr 19 '13 at 13:49
  • 2
    Great simplification – Timo Huovinen Nov 10 '14 at 19:52
  • 1
    Code is i think self explained, atleast for me. Thanks for sharing complete script. – Hassan Dad Khan Aug 8 '17 at 6:55
  • Use '$wpdb->esc_like (' instead of 'esc_sql( like_escape(' – fdrv Feb 19 '18 at 17:16
  • @fdrv You are right but according to wp docs $wpdb->esc_like still need esc_sql(). So I think the correct code would be esc_sql( $wpdb->esc_like( $search_term ) ) – Waqas Bukhary Oct 18 at 18:31
16

Wanted to update this code you guys worked on for the wordpress 4.0 and above as esc_sql() is deprecated in 4.0 higher.

function title_filter($where, &$wp_query){
    global $wpdb;

    if($search_term = $wp_query->get( 'search_prod_title' )){
        /*using the esc_like() in here instead of other esc_sql()*/
        $search_term = $wpdb->esc_like($search_term);
        $search_term = ' \'%' . $search_term . '%\'';
        $where .= ' AND ' . $wpdb->posts . '.post_title LIKE '.$search_term;
    }

    return $where;
}

Rest of the stuff is same.

Also I want to point out you can use s variable within WP_Query arguments to pass search terms, which will also search for post title i believe.

Like this:

$args = array(
    'post_type' => 'post',
    's' => $search_term,
    'post_status' => 'publish',
    'orderby'     => 'title', 
    'order'       => 'ASC'        
);
$wp_query = new WP_Query($args);
10

With some vulnerable solution posted here, I come with a bit simplified and sanitized version.

First, we create a function for the posts_where filter which allows you to only show posts matching specific conditions:

function cc_post_title_filter($where, &$wp_query) {
    global $wpdb;
    if ( $search_term = $wp_query->get( 'cc_search_post_title' ) ) {
        $where .= ' AND ' . $wpdb->posts . '.post_title LIKE \'%' . $wpdb->esc_like( $search_term ) . '%\'';
    }
    return $where;
}

Now we add cc_search_post_title into our query arguments:

$args = array(
    'cc_search_post_title' => $search_term, // search post title only
    'post_status' => 'publish',
);

And finally wrap the filter around the query:

add_filter( 'posts_where', 'cc_post_title_filter', 10, 2 );
$query = new WP_Query($args);
remove_filter( 'posts_where', 'cc_post_title_filter', 10 );

Using get_posts()

Certain functions which retrieve posts do not run filters, so the posts_where filter functions you attach will not modify the query. If you plan to use get_posts() to query your posts, you need to set suppress_filters to false in your argument array:

$args = array(
    'cc_search_post_title' => $search_term,
    'suppress_filters' => FALSE,
    'post_status' => 'publish',
);

Now you can use get_posts():

add_filter( 'posts_where', 'cc_post_title_filter', 10, 2 );
$posts = get_posts($args);
remove_filter( 'posts_where', 'cc_post_title_filter', 10 );

What about the s parameter?

The s parameter is available:

$args = array(
    's' => $search_term,
);

While adding your search term into the s parameter work and it will search the post title, it will also search the post content.

What about the title parameter which was added with WP 4.4?

Passing a search term into the title parameter:

$args = array(
    'title' => $search_term,
);

Is case sensitive and LIKE, not %LIKE%. This mean search for hello will not return post with title Hello World or Hello.

  • Excellent. I was looking for 'post_title' as a parameter and, obviously, didn't find anything. – MastaBaba Sep 13 at 15:55
7

Building on other answers before me, to provide flexibility in the situation where you want to search a post that contains a word in a meta field OR in the title of the post, I give that option via the argument "title_filter_relation." In this implementation, I only allow for "OR" or "AND" inputs with a default of "AND."

function title_filter($where, &$wp_query){
    global $wpdb;
    if($search_term = $wp_query->get( 'title_filter' )){
        $search_term = $wpdb->esc_like($search_term); //instead of esc_sql()
        $search_term = ' \'%' . $search_term . '%\'';
        $title_filter_relation = (strtoupper($wp_query->get( 'title_filter_relation'))=='OR' ? 'OR' : 'AND');
        $where .= ' '.$title_filter_relation.' ' . $wpdb->posts . '.post_title LIKE '.$search_term;
    }
    return $where;
}

Here's an example of the code in action for a very simple post type "faq" where the question is the post title itself:

add_filter('posts_where','title_filter',10,2);
$s1 = new WP_Query( array(
    'post_type' => 'faq',
    'posts_per_page' => -1,
    'title_filter' => $q,
    'title_filter_relation' => 'OR',
    'post_status' => 'publish',
    'orderby'     => 'title', 
    'order'       => 'ASC',
    'meta_query' => array(
        'relation' => 'OR',
        array(
            'key' => 'faq_answer',
            'value' => $q,
            'compare' => 'LIKE'
        )
    )
));
remove_filter('posts_where','title_filter',10,2);
  • 1
    Good insight, adding custom "query vars" to the query args passed to WP_Query in order to be able to access them within the posts_where filter. – Tom Auger Feb 4 '17 at 21:32

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