Extending search query with additional $sentence value

5 Views

Next in my quest to extend WordPress is altering how WordPress uses the the $sentence variable in posts_search to include an additional string, when the $sentence variable meets a certain criteria.

The specific usecase here is that when someone types in something like TL123 it should also search for TL-123, with TL% being the wildcard here. This is to cut-down on search mistakes (for those that do not include the hyphen).

Read More

I’ve seen how we can filter into posts_search with additional SQL queries (I’m actually using this one with pretty good success so far), but am a little fuzzy on how this could work. Any help would be greatly appreciated. Thanks!

Edit – to include more information about needs thanks to @kaiser

  • Searches are typically done by typing in TL123, when in reality the actual post title is TL-123 in most cases – so, the goal here is to intercept when the search query includes TL(NUMBER) and also search TL-(NUMBER).

Again, the trick here is that some posts do not include TL- but only TL, so that’s why I’m looking for search queries that match that pattern to also search an additional “partial phrase”.

Thanks!

Update

Alright, so based off of kaiser’s starter function, I have come up with the following: 

function wpse66815_search_query_string( $search, &$wp_query )
{
    if (!is_admin() && is_search()) {

        print_r($search);

        global $wp_query;

        // get search term
        $search_term = array_shift($wp_query->query_vars['search_terms']);
        // specify string we'll use to replace
        $replace_var = 'TL';

        // find matches for that string
        preg_match_all("/{$replace_var}(?:[^0-9]*)(d+)/i", $search_term, $out);

        // if there's no matches, return the normal search
        if ( empty($out[0]) )
            return $search;

        // find/generate the search term with the replacement
        $modified_search_term = preg_replace("/{$replace_var}(?:[^0-9]*)(d+)/i", "{$replace_var}-$1", $search_term);

        // combine both the regular and modified search term
        $new_search[] = $search_term;
        $new_search[] = $modified_search_term;

        //var_dump($new_search);

        // generate the new search query
        foreach ( $new_search as $keyword )
        {
            $new_string_parts[] = $GLOBALS['wpdb']->prepare(
                 "
                    AND ((%s.post_title LIKE '%%%s%%') OR (%s.post_content LIKE '%%%s%%'))
                 "
                ,"{$GLOBALS['wpdb']->prefix}posts"
                ,like_escape( $keyword )
                ,"{$GLOBALS['wpdb']->prefix}posts"
                ,like_escape( $keyword )
            );
        }

        // set $search equal to results
        $search = implode( " ", $new_string_parts );

        //print_r($search);
    }

    return $search;
}
add_filter('posts_search', 'wpse66815_search_query_string',500,2);

The part that I can’t get past is actually is the actual SQL query -> error below:

WordPress database error: [You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.post_title LIKE 'tl123') OR ('wp__posts'.post_content LIKE 'tl123')) ' at line 2]

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts WHERE 1=1 AND (('wp__posts'.post_title LIKE 'tl123') OR ('wp__posts'.post_content LIKE 'tl123')) AND (('wp__posts'.post_title LIKE 'TL-123') OR ('wp__posts'.post_content LIKE 'TL-123')) AND wp_posts.post_type IN ('post', 'page', 'attachment', 'galleries', 'idea_gallery', 'moulding_profiles', 'moulding_collection', 'moulding_combination') AND (wp_posts.post_status = 'publish' OR wp_posts.post_author = 2 AND wp_posts.post_status = 'private') ORDER BY wp_posts.post_date DESC LIMIT 0, 80

Any pointers on where I went wrong? Thanks!

Update #2

I’ve started to see a few issues:

  • It was doing wp__posts instead of wp_posts (I updated that above)
  • As kaiser mentioned, the LIKE %s portion may need to be LIKE %%s% but that is not parsing correctly.

Error is now:

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts WHERE 1=1 AND (('wp_posts'.post_title LIKE 'tl123') OR ('wp_posts'.post_content LIKE 'tl123')) AND (('wp_posts'.post_title LIKE 'TL-123') OR ('wp_posts'.post_content LIKE 'TL-123')) AND wp_posts.post_type IN ('post', 'page', 'attachment', 'galleries', 'idea_gallery', 'moulding_profiles', 'moulding_collection', 'moulding_combination') AND (wp_posts.post_status = 'publish' OR wp_posts.post_author = 2 AND wp_posts.post_status = 'private') ORDER BY wp_posts.post_date DESC LIMIT 0, 80

The error above (from what I can tell) is actually trying to look for a post that is both tl123 and TL-123, but I think OR is what I want (as I want to return posts in an either or situation).

Update #3

Updated function to properly escape %s, so error is now:

WordPress database error: [You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.post_title LIKE '%tl123%') OR ('wp_posts'.post_content LIKE '%tl123%')) ' at line 2]

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts WHERE 1=1 AND (('wp_posts'.post_title LIKE '%tl123%') OR ('wp_posts'.post_content LIKE '%tl123%')) AND (('wp_posts'.post_title LIKE '%TL-123%') OR ('wp_posts'.post_content LIKE '%TL-123%')) AND wp_posts.post_type IN ('post', 'page', 'attachment', 'galleries', 'idea_gallery', 'moulding_profiles', 'moulding_collection', 'moulding_combination') AND (wp_posts.post_status = 'publish' OR wp_posts.post_author = 2 AND wp_posts.post_status = 'private') ORDER BY wp_posts.post_date DESC LIMIT 0, 80

Update #4

Here’s what I ended up doing -> the foreach wasn’t wrapping correctly (with the AND/OR operator as @kaiser mentioned), so I separated them out into their own array insertions. 

function wpse66815_search_query_string( $search, &$wp_query )
{
    if (!is_admin() && is_search()) {

        //print_r($search);

        global $wp_query,$wpdb;

        // get search term
        $search_term = array_shift($wp_query->query_vars['search_terms']);
        // specify string we'll use to replace
        $replace_var = 'TL';

        // find matches for that string
        preg_match_all("/{$replace_var}(?:[^0-9]*)(d+)/i", $search_term, $out);

        // if there's no matches, return the normal search
        if ( empty($out[0]) )
            return $search;

        // find/generate the search term with the replacement
        $modified_search_term = preg_replace("/{$replace_var}(?:[^0-9]*)(d+)/i", "{$replace_var}-$1", $search_term);

        // combine both the regular and modified search term
        $new_search[] = $search_term;
        $new_search[] = $modified_search_term;

        var_dump($new_search);

        // generate the new search query
        $new_string_parts[] = $wpdb->prepare( "AND ((({$wpdb->posts}.post_title LIKE '%%%s%%') OR ({$wpdb->posts}.post_content LIKE '%%%s%%'))",like_escape( $new_search[0] ),like_escape( $new_search[0] ));
        $new_string_parts[] = $wpdb->prepare( "OR (({$wpdb->posts}.post_title LIKE '%%%s%%') OR ({$wpdb->posts}.post_content LIKE '%%%s%%')))",like_escape( $new_search[1] ),like_escape( $new_search[1] ));

        // set $search equal to results
        $search = implode( " ", $new_string_parts );

        //print_r($search);
    }

    return $search;
}
add_filter('posts_search', 'wpse66815_search_query_string',500,2);

It does have some trouble if someone were to type in tl123 tl456 (two occurences of the tl keyword), but I’m working on that portion. Thanks!

Post Views: 5

Related posts

Leave a Reply

1 comment

  1. Gladly I’ve written two plugins for that yesterday:

    Filter/Core

    That’s what a search query part looks like inside the posts_search filter:

    ' AND (((wp_XX_posts.post_title LIKE '%test%') OR (wp_XX_posts.post_content LIKE '%test%'))) '

    where wp_XX_ is just the $wpdb->prefix for my WPSE test site inside my local MU installation.

    Plugin #1 – drop searching post types that we don’t need.

    Here’s a plugin modifying the searched post types, as this is needed often.

    <?php
/** Plugin Name: (#66815) »kaiser« Limit search query post types */

/**
 * Alter the searched post types
 * 
 * @param  object $query
 * @return object $query
 */
add_action( 'pre_get_posts', 'wpse66815_pre_get_posts' );
function wpse66815_pre_get_posts( $query )
{
    if ( $query->is_main_query() )
    {
        $query->set( 'post_type', 'YOUR_POST_TYPE' );
    }

    return $query;
}

    Plugin #2 – modify the search string

    Now, that we know how the default search string for the post title and content looks, we just have to rebuild it the way we need it:

    <?php 
/** Plugin Name: (#66815) »kaiser« Modify search query string */

function wpse66815_search_query_string( $search_string )
{
    global $wpdb;

    $searched_for = preg_match_all(
        // Match a prefix (%), but exclude it from the capture
        // Any character, any number of repetitions
        // Match a suffix (%), but exclude it from the capture
        "/(?<=%)(.*)(?=%)/",
        $search_string,
        $search_string_matches
    );

    // We only need one element
    $searched_for = array_shift( $search_string_matches );

    // Now we need to search for [LETTERS (min 1)][NUMBER (zero or more)][CHARACTER (zero or more)]
    preg_match_all(
        "/([^a-zA-Z][d+]*[-_ ]*)/",
        $searched_for,
        $string_part_matches
    );

    // Here we now got matches - if not, we can simply abort and leave the default
    $searched_for = array_shift( $string_part_matches );
    if ( empty( $searched_for ) )
        return $search_string;

    // Finally we need to split the string by all parts that are allowed
    // YOU NEED TO EDIT MY ANSWER HERE AND FILL IN WHAT WORKS FOR YOU
    $keywords = preg_split(
         "/([s]*[d+]*[-_ ]*)/",
         $searched_for,
         -1, // 0 & -1 are NO limit
         PREG_SPLIT_NO_EMPTY | PREG_SPLIT_DELIM_CAPTURE
    );

    // Now loop and build an array for further processing
    // Our first search string is - of course - the default string
    $string_parts = array( $search_string );
    foreach ( $keywords as $keyword )
    {
        $new_string_parts[] = $GLOBALS['wpdb']->prepare(
            " AND ((%s.post_title LIKE '%s') OR (%s.post_content LIKE '%s')) ",
            $wpdb->posts,
            $wpdb->esc_like( $keyword ),
            $wpdb->posts,
            $wpdb->esc_like( $keyword )
        );
    }

    // Now lets glue them together, return and see what we get...
    return implode( " ", $new_string_parts );
}

    This 2nd plugin is untested, as my needs are different and I guess some of the regexes are not completely what you need – you’ll have to fix that and update this answer (this is your part in “giving the community back” in this Q/A). A nice tool to build regex, that I just found, is Expresso. It’s damn ugly (as the web presence), but extremely helpful.