0

I've got a function that returns a count from the database for publish posts that match the product ID to the post meta ID (both are the product type). I've realized this query returns unpublished posts as well.

I've tried for a couple of hours to figure out how I can include a defensive check to only include posts that have the post_status of publish

public function get_number_of_stars_for( $star_value ) {
        global $wpdb;

        return $wpdb->get_var(
            $wpdb->prepare(
                "SELECT COUNT(wpm2.`meta_value`)
                    FROM $wpdb->postmeta as wpm2,
                    (
                        SELECT * FROM $wpdb->postmeta WHERE `meta_value` = %d AND `meta_key` = 'review_product'
                    ) as wpm1
                    WHERE wpm1.`post_id` = wpm2.`post_id` AND wpm2.`meta_key` = 'review_rating' AND wpm2.`meta_value` = %d
                ",
                $this->post->ID,             
                $star_value
            )
        );
    }
0

Short Answer

To get the post status, you'll need to JOIN the posts table based on the post_id you grabbed from wpm1 or wpm2 and extend your WHERE clause to use that joined data.

Recommendation

On the other hand, instead of going directly to the database to get your content, you could use WP_Query to process this content. See below for a quick example. I highly recommend testing this code before adding it to your site.

<?php
$args = array(
    'fields' => 'ids',
    'posts_per_page' => 1, // This is a heavy-handed approach to limit result to one
    'meta_query' => array(
        array(
            'key'     => 'review_product',
            'value'   => $this->post->ID,
            'compare' => '=',
        ),
    ),
);
$query = new WP_Query( $args );
$all_ids = $query->posts[0]; // Most likely want to change this to a foreach so you can find the right value

$args2 = array(
    'fields' => 'ids',
    'meta_query' => array(
        array(
            'key'     => 'review_rating',
            'value'   => $all_ids,
            'compare' => '=',
        ),
    ),
);
$query2 = new WP_Query( $args2 );
return $query2->post_count;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.