在WP_QUERY META_QUERY键中使用REGEXP

时间：2015-07-07 作者：Philipp Kühn

我知道我可以在WP\\u查询中使用REGEXP，如下所示：

$query = new WP_Query(array(
  \'posts_per_page\'    => -1,
  \'post_status\'       => \'publish\',
  \'meta_query\'        => array(
    array(
      \'key\'     => \'custom_fields\',
      \'value\'   => \'foo[(][0-9][)]\', // with regex stuff
      \'compare\' => \'REGEXP\',
    ),
  ),
));
但我也需要在键中使用正则表达式。像这样：

$query = new WP_Query(array(
  \'posts_per_page\'    => -1,
  \'post_status\'       => \'publish\',
  \'meta_query\'        => array(
    array(
      \'key\'     => \'custom_fields[(][0-9][)]\', // with regex stuff
      \'value\'   => \'foo\',
      \'compare\' => \'REGEXP\',
    ),
  ),
));
有没有什么方法可以通过过滤器实现这一点？或者我必须自己用自己的SQL查询来构建它吗？

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

这里有一个实验想法：

假设我们得到：

邮递A 使用自定义字段location1 英国-伦敦

邮递B 使用自定义字段location2 法国-巴黎

邮递C 使用自定义字段location3 美国-纽约

然后我们可以使用，例如：

$args = [
    \'meta_query\' => [
        \'relation\' => \'OR\',
        [
            \'key\'          => "^location[0-9]",
            \'_key_compare\' => \'REGEXP\',
            \'value\'        => \'London\',
            \'compare\'      => \'LIKE\',
        ],
        [
            \'key\'          => \'location%\',
            \'_key_compare\' => \'LIKE\',
            \'value\'        => \'Paris\',
            \'compare\'      => \'LIKE\'
        ],
        [
            \'key\'          => \'location3\',
            \'value\'        => \'New York\',
            \'compare\'      => \'LIKE\'
        ]
    ]
];
我们支持定制_key_compare 具有以下插件的参数：

<?php
/**
 *  Plugin Name:   Extended Meta Key Search In WP_Query
 *  Description:   Custom \'_key_compare\' argument as REGEXP, RLIKE or LIKE
 *  Plugin URI:    http://wordpress.stackexchange.com/a/193841/26350
 *  Plugin Author: Birgir Erlendsson (birgire)
 *  Version:       0.0.3
 */

add_action( \'pre_get_posts\', function( $q )
{
    // Check the meta query:
    $mq = $q->get( \'meta_query\' );

    if( empty( $mq ) )
        return;

    // Init:
    $marker = \'___tmp_marker___\'; 
    $rx     = [];

    // Collect all the sub meta queries, that use REGEXP, RLIKE or LIKE:
    foreach( $mq as $k => $m )                                    
    {
        if(    isset( $m[\'_key_compare\'] )
            && in_array( strtoupper( $m[\'_key_compare\'] ), [ \'REGEXP\', \'RLIKE\', \'LIKE\' ] )
            && isset( $m[\'key\'] )
        ) {
            // Mark the key with a unique string to secure the later replacements:
            $m[\'key\'] .= $marker . $k; // Make the appended tmp marker unique

            // Modify the corresponding original query variable:
            $q->query_vars[\'meta_query\'][$k][\'key\'] = $m[\'key\'];

            // Collect it:
            $rx[$k] = $m;
        }
    }

    // Nothing to do:
    if( empty( $rx ) )
        return;

    // Get access the generated SQL of the meta query:
    add_filter( \'get_meta_sql\', function( $sql ) use ( $rx, $marker )
    {
        // Only run once:
        static $nr = 0;         
        if( 0 != $nr++ )
            return $sql;

        // Modify WHERE part where we replace the temporary markers:
        foreach( $rx as $k => $r )
        {
            $sql[\'where\'] = str_replace(
                sprintf(
                    ".meta_key = \'%s\' ",
                    $r[\'key\']
                ),
                sprintf(
                    ".meta_key %s \'%s\' ",
                    $r[\'_key_compare\'],
                    str_replace(
                        $marker . $k,
                        \'\',
                        $r[\'key\']
                    )
                ),
                $sql[\'where\']
            );
        }
        return $sql;
    });

});
在这里，我们在每个元键上添加唯一的标记以替换字符串。

请注意，这不支持regex字符转义，例如\\( 和\\\\.

SO网友:Rogelio Vargas

您的答案在第一个数组lvl中是完美的，例如：

$args[\'meta_query\'][] = array(

  \'key\' => \'tour_itinerario_ciudades_repeater_%_tour_ciudades_nombre\',
  \'_key_compare\' => \'LIKE\',
  \'value\' => \'MEXICO\',
  \'compare\' => \'LIKE\',
  );
我需要对阵列中第二层的工作进行一些修改：

$args[\'meta_query\'][] = array(
    \'relation\' => \'OR\',
    array(
        \'key\' => \'tour_itinerario_ciudades_repeater_%_tour_ciudades_nombre\',
        \'_key_compare\' => \'LIKE\',
        \'value\' => \'CONDESA\',
        \'compare\' => \'LIKE\',
    ),
    array(
        \'key\' => \'tour_itinerario_ciudades_repeater_%_tour_ciudades_nombre\',
        \'_key_compare\' => \'LIKE\',
        \'value\' => \'Ciudad 1\',
        \'compare\' => \'LIKE\',
    )
);
现在，

add_action(\'pre_get_posts\', function( $q ) {
// Check the meta query:
$mq = $q->get(\'meta_query\');

if (empty($mq))
    return;

// Init:
$marker = \'___tmp_marker___\';
$rx = [];

// Collect all the sub meta queries, that use REGEXP, RLIKE or LIKE:
// Only works for 1st level in array
foreach ($mq as $k => $m) {
    if (isset($m[\'_key_compare\']) && in_array(strtoupper($m[\'_key_compare\']), [ \'REGEXP\', \'RLIKE\', \'LIKE\']) && isset($m[\'key\'])
    ) {
        // Mark the key with a unique string to secure the later replacements:
        $m[\'key\'] .= $marker . $k; // Make the appended tmp marker unique
        // Modify the corresponding original query variable:
        $q->query_vars[\'meta_query\'][$k][\'key\'] = $m[\'key\'];

        // Collect it:
        $rx[$k] = $m;
    }
}

// custom code to make it work with arguments on Multidimensional array 
foreach ($mq as $k => $m) {
    foreach ($m as $k_i => $m_i) {
        if (count($m_i) >= 3) {
            if (isset($m_i[\'_key_compare\']) && in_array(strtoupper($m_i[\'_key_compare\']), [ \'REGEXP\', \'RLIKE\', \'LIKE\']) && isset($m_i[\'key\'])
            ) {
                // Mark the key with a unique string to secure the later replacements:
                $m_i[\'key\'] .= $marker . $k_i; // Make the appended tmp marker unique
                // Modify the corresponding original query variable:
                $q->query_vars[\'meta_query\'][$k][$k_i][\'key\'] = $m_i[\'key\'];

                // Collect it:
                $rx[$k][$k_i] = $m_i;
            }
        }
    }
}


// Nothing to do:
if (empty($rx))
    return;

// Get access the generated SQL of the meta query:
add_filter(\'get_meta_sql\', function( $sql ) use ( $rx, $marker ) {
    // Only run once:
    static $nr = 0;
    if (0 != $nr++)
        return $sql;

    // Modify WHERE part where we replace the temporary markers:
    //PRIMER NIVEL
    foreach ($rx as $k => $r) {
        $sql[\'where\'] = str_replace(
                sprintf(
                        ".meta_key = \'%s\' ", $r[\'key\']
                ), sprintf(
                        ".meta_key %s \'%s\' ", $r[\'_key_compare\'], str_replace(
                                $marker . $k, \'\', $r[\'key\']
                        )
                ), $sql[\'where\']
        );
    }
    //SECOND NIVEL
    foreach ($rx as $k => $r) {
        //TODO: test with several cases since may have bugs
        if (!isset($r[\'key\'])) {//FORZO LA ENTRADA 
            foreach ($r as $k_i => $r_i) {
                $sql[\'where\'] = str_replace(
                        sprintf(
                                ".meta_key = \'%s\' ", $r_i[\'key\']
                        ), sprintf(
                                ".meta_key %s \'%s\' ", $r_i[\'_key_compare\'], str_replace(
                                        $marker . $k_i, \'\', $r_i[\'key\']
                                )
                        ), $sql[\'where\']
                );
            }
        }
    }

    var_dump($sql);
    return $sql;
});
}）；如果有人需要类似的答案，

再次使用THK

结束
标签： wp-query   filters   post-meta   mysql   meta-query   小码农  

相关推荐

使用Windows身份验证连接到MySQL

是否可以使用Wordpress/PHP连接到我的MySQL数据库Windows Authentication 在IIS上？我可以使用普通的MySQL命令行实用程序很好地连接。我得到了以下错误：Warning: mysqli_real_connect(): The server requested authentication method unknown to the client [authentication_windows_client] in c:\\wordpress\\wp-includes\