从WHERE子句中删除POST_CONTENT搜索(和CONCAT SQL函数)

时间：2017-01-07 作者：codeispoetry

我有一个自定义表格，并按照本页上的说明进行操作https://codex.wordpress.org/Custom_Queries 我可以修改WHERE子句。

function geotag_search_where( $where ){
   if( is_search() ) {
         $where = preg_replace( "/\\(\\s*" . $wpdb->posts . ".post_title\\s+LIKE\\s*(\\\'[^\\\']+\\\')\\s*\\)/",
         " CONCAT( posts.post_title, customtable.customfield) LIKE $1 ", $where );
      }

   return $where;
}

add_filter(\'posts_where\', \'geotag_search_where\' );
我唯一的问题是wordpress保留了OR (posts.post_content LIKE \'%searchterm%\') 这会减慢我的搜索时间。有没有办法修改preg_replace() 这样它也会排除post\\u内容？谢谢

编辑[根据@Prosti请求]

下面是我的WHERE子句

SELECT [normal select from worpress] 
 ….
[customized from my filters]
LEFT JOIN customtable ON FIND_IN_SET(posts.ID, customtable.postID) >0 
WHERE 1=1 
AND (term_relationships.term_taxonomy_id IN (7) ) 
AND (( CONCAT(posts.post_title, customtable.customfield) LIKE \'%searchterm%\' 
    OR (posts.post_content LIKE \'%searchterm%\'))) 
[end of customization]
 ……. 
AND [rest is normal from worpress]
我想排除OR (posts.post_content LIKE \'%searchterm%\')

因此，sql将是：

....
AND (( CONCAT(posts.post_title, customtable.customfield) LIKE \'%searchterm%\' ))
....

1 个回复
SO网友:prosti

首先看看preg替换有多复杂。通常，您可能会在这方面出错。

NODE                     EXPLANATION
--------------------------------------------------------------------------------
  /                        \'/\'
--------------------------------------------------------------------------------
  \\(                       \'(\'
--------------------------------------------------------------------------------
  \\s*                      whitespace (\\n, \\r, \\t, \\f, and " ") (0 or
                           more times (matching the most amount
                           possible))
--------------------------------------------------------------------------------
  post_title               \'post_title\'
--------------------------------------------------------------------------------
  \\s+                      whitespace (\\n, \\r, \\t, \\f, and " ") (1 or
                           more times (matching the most amount
                           possible))
--------------------------------------------------------------------------------
  LIKE                     \'LIKE\'
--------------------------------------------------------------------------------
  \\s*                      whitespace (\\n, \\r, \\t, \\f, and " ") (0 or
                           more times (matching the most amount
                           possible))
--------------------------------------------------------------------------------
  (                        group and capture to \\1:
--------------------------------------------------------------------------------
    \\\'                       \'\'\'
--------------------------------------------------------------------------------
    [^\\\']+                   any character except: \'\\\'\' (1 or more
                             times (matching the most amount
                             possible))
--------------------------------------------------------------------------------
    \\\'                       \'\'\'
--------------------------------------------------------------------------------
  )                        end of \\1
--------------------------------------------------------------------------------
  \\s*                      whitespace (\\n, \\r, \\t, \\f, and " ") (0 or
                           more times (matching the most amount
                           possible))
--------------------------------------------------------------------------------
  \\)                       \')\'
--------------------------------------------------------------------------------
  /",       "              \'/",       "\'
--------------------------------------------------------------------------------
  (                        group and capture to \\2:
--------------------------------------------------------------------------------
    post_title LIKE          \'post_title LIKE \'
--------------------------------------------------------------------------------
    $                        before an optional \\n, and the end of
                             the string
--------------------------------------------------------------------------------
    1                        \'1\'
--------------------------------------------------------------------------------
  )                        end of \\2
--------------------------------------------------------------------------------
   OR                      \' OR \'
--------------------------------------------------------------------------------
  (                        group and capture to \\3:
--------------------------------------------------------------------------------
    geotag_city LIKE         \'geotag_city LIKE \'
--------------------------------------------------------------------------------
    $                        before an optional \\n, and the end of
                             the string
--------------------------------------------------------------------------------
    1                        \'1\'
--------------------------------------------------------------------------------
  )                        end of \\3
--------------------------------------------------------------------------------
   OR                      \' OR \'
--------------------------------------------------------------------------------
  (                        group and capture to \\4:
--------------------------------------------------------------------------------
    geotag_state LIKE        \'geotag_state LIKE \'
--------------------------------------------------------------------------------
    $                        before an optional \\n, and the end of
                             the string
--------------------------------------------------------------------------------
    1                        \'1\'
--------------------------------------------------------------------------------
  )                        end of \\4
--------------------------------------------------------------------------------
                OR         \'              OR \'
--------------------------------------------------------------------------------
  (                        group and capture to \\5:
--------------------------------------------------------------------------------
    geotag_country           \'geotag_country LIKE \'
    LIKE
--------------------------------------------------------------------------------
    $                        before an optional \\n, and the end of
                             the string
--------------------------------------------------------------------------------
    1                        \'1\'
--------------------------------------------------------------------------------
  )                        end of \\5
接下来，您需要提供full 之后的SQL查询示例preg_replace .

如果我们可以拥有完整的SQL查询，那么我们也可以理解定制。

查看您编写的内容，我们需要您为WP\\u查询设置的参数，以了解您是如何组织查询的。

更多CONCAT 这不是一件聪明的事情，它迫使MySQL连接字符串；减速系数。

希望这可能会有所帮助。

标签： filters   search   posts-where   小码农  

相关推荐

为内置钩子调用do_action和Apply_Filters是否安全？

我正在开发一个插件，它需要复制一些内置的WordPress逻辑。（此逻辑不能用任何内置方法调用，也不能独立连接到。）在这个动作序列中，WordPress的正常行为是调用动作挂钩(do_action(\'wp_login\', ...)) 和过滤器挂钩(apply_filters(\'login_redirect\', ...)).如果在对应于在Core中调用它们的时间点调用它们，那么直接从我的插件调用这些内置钩子是否安全（并且是可以接受的做法）？或者，其他与此相关的开发人员期望在非常特定的时间执行操作的风