定制SELECT查询上的三重元关键字

时间：2016-10-20 作者：Swen

我使用一个奇怪的SELECT查询来计算所有帖子评分的平均值（存储在wp_postmeta) 对于某个用户。

我的查询基本上使用以下参数：

post_author = 1 和meta_key = \'rating\' 和meta_value != 0.

这个查询本身工作得非常好，但这里是它变得复杂的地方。我需要添加一些例外。。。

meta_key = \'anonymous\' 和meta_value != \'true\'

还有另一个。。。

meta_key = \'original_author\' 和meta_value = \'\'

我只想检索rating meta\\u值，因此使用$wpdb->postmeta.meta_value.

总计3个meta_key 和meta_value 参数，只有一个meta_value 我真的想找回。事情变得越来越棘手。。。

请参见下面的我的代码：

// Example value
    $user_id = 1;

// Calculate average post rating for user
    $ratings_query = $wpdb->get_results(
                        $wpdb->prepare("
                            SELECT $wpdb->postmeta.meta_value 
                            FROM $wpdb->postmeta
                                JOIN $wpdb->posts ON ($wpdb->postmeta.post_id = $wpdb->posts.id)
                                    WHERE ( 
                                        $wpdb->posts.post_author = %d AND 
                                        $wpdb->posts.post_type = \'post\' AND 
                                        $wpdb->posts.post_status = \'publish\' AND
                                        $wpdb->postmeta.meta_key = \'rating\' AND 
                                        $wpdb->postmeta.meta_value != 0

                                        AND

                                        $wpdb->postmeta.meta_key = \'anonymous\' AND 
                                        $wpdb->postmeta.meta_value != \'true\'

                                        AND

                                        $wpdb->postmeta.meta_key = \'original_author\' AND 
                                        $wpdb->postmeta.meta_value = \'\')
                        ", $user_id), ARRAY_N);

    if ( $ratings_query ) {
        $ratings_query = call_user_func_array(\'array_merge\', $ratings_query);       
        $average_rating = round(array_sum($ratings_query) / count($ratings_query), 1);
    } else {
        $average_rating = 0;
    }

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

您的查询不正确。要检索triple meta\\u键的meta值，需要使用posts-meta-on-posts表进行3个不同的连接。检查以下代码：

// Example value
$user_id = 1;

// Calculate average post rating for user
$ratings_query = $wpdb->get_results(
                     $wpdb->prepare("
SELECT pmeta.meta_value  
    FROM wp_posts
      LEFT JOIN $wpdb->postmeta AS pmeta 
        ON (pmeta.post_id = $wpdb->posts.id) 
      LEFT JOIN $wpdb->postmeta AS pmeta1 
        ON (pmeta1.post_id = $wpdb->posts.id) 
      LEFT JOIN $wpdb->postmeta AS pmeta2 
        ON (pmeta2.post_id = $wpdb->posts.id) 
    WHERE $wpdb->posts.post_author = %d AND 
    $wpdb->posts.post_type = \'post\' AND 
    $wpdb->posts.post_status = \'publish\'
      AND (
        pmeta.meta_key = \'rating\' 
        AND CAST(pmeta.meta_value AS CHAR) != \'0\'
      ) 
      AND (
        pmeta1.meta_key = \'anonymous\' 
        AND CAST(pmeta1.meta_value AS CHAR) != \'true\'
      ) 
      AND (
        pmeta2.meta_key = \'original_author\' 
        AND CAST(pmeta2.meta_value AS CHAR) = \'\'
      )", $user_id), ARRAY_N);
要使用自定义查询检索元数据，您需要为posts表上的每个元数据对提供1个联接，如上所述。

如果使用代码打印上一个SQL查询echo $wpdb->last_query; , 您将获得SQL查询：

SELECT 
  pmeta.meta_value 
FROM
  wp_posts 
  LEFT JOIN wp_postmeta AS pmeta 
    ON (pmeta.post_id = wp_posts.id) 
  LEFT JOIN wp_postmeta AS pmeta1 
    ON (pmeta1.post_id = wp_posts.id) 
  LEFT JOIN wp_postmeta AS pmeta2 
    ON (pmeta2.post_id = wp_posts.id) 
WHERE wp_posts.post_author = 1 
  AND wp_posts.post_type = \'post\' 
  AND wp_posts.post_status = \'publish\' 
  AND (
    pmeta.meta_key = \'rating\' 
    AND CAST(pmeta.meta_value AS CHAR) != \'0\'
  ) 
  AND (
    pmeta1.meta_key = \'anonymous\' 
    AND CAST(pmeta1.meta_value AS CHAR) != \'true\'
  ) 
  AND (
    pmeta2.meta_key = \'original_author\' 
    AND CAST(pmeta2.meta_value AS CHAR) = \'\'
  )

SO网友:Bysander

另一个答案没有问题，但我想我应该将其添加为另一种方法，因为它很简单，并且使用WP来计算SQL查询。由于有额外的查询get_post_meta 作用

$user_id = 1;
$args = array(
              \'post_type\' => \'post\',
              \'posts_per_page\' => -1,
              \'post_status\' => \'publish\',
              \'orderby\' => \'date\',
              \'order\' => \'ASC\',
              \'meta_query\' => array(
                                    \'relation\' => \'AND\',
                                    array(
                                          \'key\' => \'post_author\',
                                          \'value\' => $user_id
                                          ),
                                    array(
                                          \'key\' => \'rating\',
                                          \'compare\' => \'!=\', // or\'>\'
                                          \'value\' => 0
                                          ),
                                    array(
                                          \'key\' => \'anonymous\',
                                          \'compare\' => \'!=\',
                                          \'value\' => true
                                          ),
                                    array(
                                          \'key\' => \'original_author\',
                                          \'compare\' => \'!=\',
                                          \'value\' => \'\'
                                          )
                                    )
              );

$RelevantPosts = get_posts($args);

$ratings = array();
foreach($RelevantPosts as $RelevantPost) {
    $ratings[] = get_post_meta($RelevantPost->ID, \'rating\' , true);
}

if ( $RelevantPosts ) {
    $average_rating = round(array_sum($ratings) / count($ratings), 1);
} else {
    $average_rating = 0;
}
然而，它很容易阅读和理解，只要它不是大规模运行（如一次针对所有帖子），它就可以用于资源。

标签： post-meta   mysql   wpdb   select   小码农  

相关推荐

Wp_Dropdown_Categories()中的‘SELECTED’参数用于什么？

我正在研究其他人的代码（基于Elementor的自定义搜索小部件），但有一件事我不明白。$type_term = get_term_by(\'slug\', get_query_var($taxonomy), $taxonomy); $type = (!empty($type_term) && !empty($type_term->slug)) ? $type_term->slug : 0; wp_dropdown_categories(array(