How to add and SQL query of posts only published

I have a notification on a menu item and need to change the number on the notification. I'm missing an argument of show only "Published" posts. Right now it's pulling in everything in draft too. Here's what I have now. Need to add the Published part to it.

    $prepare_string = "SELECT DISTINCT ID FROM $wpdb->posts LEFT JOIN $wpdb->postmeta ON ( $wpdb->posts.ID = $wpdb->postmeta.post_id ) LEFT JOIN $wpdb->postmeta AS mt1 ON ( $wpdb->posts.ID = mt1.post_id ) WHERE ( ( $wpdb->postmeta.meta_key = 'event_date' AND $wpdb->postmeta.meta_value >= %d ) OR ( ( $wpdb->postmeta.meta_key = 'event_date' AND $wpdb->postmeta.meta_value < %d ) AND ( mt1.meta_key = 'end_date' AND mt1.meta_value >= %d ) ) OR ( $wpdb->postmeta.meta_key = 'event_date' AND $wpdb->postmeta.meta_value = '' ) )";

Answers 1

  • You should be able to get away with just adding AND $wpdb->posts.post_status = 'publish' at the end.

    $prepare_string = "
        SELECT DISTINCT ID 
        FROM        $wpdb->posts 
        LEFT JOIN   $wpdb->postmeta ON ( $wpdb->posts.ID = $wpdb->postmeta.post_id ) 
        LEFT JOIN   $wpdb->postmeta AS mt1 ON ( $wpdb->posts.ID = mt1.post_id ) 
        WHERE       ( ( $wpdb->postmeta.meta_key = 'event_date' AND $wpdb->postmeta.meta_value >= %d ) 
        OR          ( ( $wpdb->postmeta.meta_key = 'event_date' AND $wpdb->postmeta.meta_value < %d ) 
        AND         ( mt1.meta_key = 'end_date' AND mt1.meta_value >= %d ) ) 
        OR          ( $wpdb->postmeta.meta_key = 'event_date' AND $wpdb->postmeta.meta_value = '' ) )
        AND         $wpdb->posts.post_status = 'publish'
    ";
    

Related Questions