post meta - Triple meta_key on custom SELECT query

admin管理员组

文章数量:1315968

I'm using a weird SELECT query to calculate the average of all the post ratings (stored in wp_postmeta) for a certain user.

My query basically uses the following arguments:

post_author = 1 AND meta_key = 'rating' AND meta_value != 0.

This query works perfectly fine on it's own, but here's where it gets complicated. I need to add some exceptions...

meta_key = 'anonymous' AND meta_value != 'true'

And another...

meta_key = 'original_author' AND meta_value = ''

I want to retrieve only the rating meta_values, so I'll probably run into more problems using $wpdb->postmeta.meta_value.

This totals up to 3 meta_key and meta_value arguments, with only one meta_value that I actually want to retrieve. It just gets more and more tricky...

See my code below:

// 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;
    }

I'm using a weird SELECT query to calculate the average of all the post ratings (stored in wp_postmeta) for a certain user.

My query basically uses the following arguments:

post_author = 1 AND meta_key = 'rating' AND meta_value != 0.

This query works perfectly fine on it's own, but here's where it gets complicated. I need to add some exceptions...

meta_key = 'anonymous' AND meta_value != 'true'

And another...

meta_key = 'original_author' AND meta_value = ''

I want to retrieve only the rating meta_values, so I'll probably run into more problems using $wpdb->postmeta.meta_value.

This totals up to 3 meta_key and meta_value arguments, with only one meta_value that I actually want to retrieve. It just gets more and more tricky...

See my code below:

// 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;
    }

• post-meta
• mysql
• wpdb
• select

Share Improve this question asked Oct 20, 2016 at 22:34 SwenSwen 1,38477 gold badges2121 silver badges3636 bronze badges 1

• It's not clear what the question is here. If there's a problem with the code snippet, please explain that in more detail. Thanks. – birgire Commented Oct 24, 2016 at 11:07

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 3

Your Query is incorrect. To retrive meta values for triple meta_key you will need 3 different joins using posts meta on posts table. Check code below:

// 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);

To retrive meta data using custom query, you will need 1 joins for each meta data pairs on posts table as above.

if you print last SQL query using code echo $wpdb->last_query; , you will get SQL query:

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) = ''
  )

There's nothing wrong with the other answer but I thought I'd add this as a different method as it's simple and uses WP to work out the SQL Query. It would be slightly heavier on resources as there are extra queries with the get_post_meta function.

$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;
}

It is however simple to read and understand and so long as it not run in mass (eg for all your posts at once) it should be fine for resources.

本文标签： post metaTriple metakey on custom SELECT query