用额外的$语句值扩展搜索查询

时间：2012-10-01 作者：Zach

下一步我要扩展WordPress，就是改变WordPress使用$sentence 变量输入posts_search 若要包含其他字符串，请在$sentence 变量符合特定标准。

这里的具体用例是，当有人键入TL123 它还应该搜索TL-123, 具有TL% 在这里是通配符。这是为了减少搜索错误（对于不包含连字符的搜索错误）。

我看到了我们如何过滤posts_search 使用其他SQL查询（我实际上使用this one 到目前为止，取得了相当好的成功），但我对如何实现这一点有点模糊。任何帮助都将不胜感激。谢谢

编辑-感谢@kaiser，以包含更多有关需求的信息

搜索通常通过键入TL123来完成，而在大多数情况下，实际的帖子标题是TL-123，因此，这里的目标是在搜索查询包括TL（数字）和搜索TL（数字）时进行拦截

谢谢

更新

好的，基于kaiser的starter功能，我得出了以下结论：

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);
我无法通过的部分实际上是实际的SQL查询->错误，如下所示：

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

有没有关于我哪里出错的指示？谢谢

更新#2

我开始发现一些问题：

它正在做wp__posts 而不是wp_posts （我更新了上面的内容）kaiser 如前所述LIKE %s 部分可能需要LIKE %%s% 但这并不能正确解析

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

上面的错误（据我所知）实际上是在寻找一篇既有tl123 和TL-123, 但我认为OR 是我想要的（因为我想在非此即彼的情况下返回帖子）。

更新#3

更新的函数以正确转义%s, 所以现在的错误是：

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

更新#4

我最后做的是->foreach没有正确包装（使用了@kaiser提到的AND/OR操作符），所以我将它们分离到各自的数组插入中。

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);
如果有人输入，确实会有一些麻烦tl123 tl456 （两次出现tl 关键字），但我正在处理那个部分。谢谢

1 个回复
最合适的回答,由SO网友:kaiser 整理而成

很高兴我昨天为此编写了两个插件：

过滤器/核心是搜索查询部分在posts_search 过滤器：

\' AND (((wp_XX_posts.post_title LIKE \'%test%\') OR (wp_XX_posts.post_content LIKE \'%test%\'))) \'
在哪里wp_XX_ 只是$wpdb->prefix 对于本地MU安装中的WPSE测试站点。

插件#1-删除搜索我们不需要的帖子类型

这里有一个插件可以修改搜索到的帖子类型，因为这是经常需要的。

<?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-修改搜索字符串现在，我们知道了帖子标题和内容的默认搜索字符串的外观，只需按照需要的方式重新构建即可：

<?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 );
}
这个2nd插件未经测试，因为我的需求不同，我想有些正则表达式并不是你所需要的-你必须修复它并更新这个答案（这是你在本问答中“回馈社区”的一部分）。我刚刚发现，构建regex的一个很好的工具是Expresso. 这很难看（就像网络一样），但非常有用。

结束
标签： search   mysql   小码农  

相关推荐

Mysqldump add drop table?

我注意到在Codex中显示了用于备份数据库的--add drop table选项。在我搞砸之前，这是否意味着在最终导入备份时，如果目标数据库中存在这些表，它们将被覆盖？我不想在备份桌子时把它们弄掉！user@linux:~/files/blog> mysqldump --add-drop-table -h mysqlhostserver -u mysqlusername -p databasename (tablename tablename tablename) | bzip2