Triple meta_key on custom SELECT query

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

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