Triple meta_key on custom SELECT query

Question

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;
    }
0
Swen 2 months 0 Answers 11 views 0

Leave an answer