get posts based on comment status

A plugin? I’m sure you can edit an existing plugin. Or, just alter wp’s query. We use something similar to find posts that comment_count of zero. You can have your query find posts that have a comment_status of open/closed.

I think you can do that by subquerying the database.
SELECT * FROM wp_posts WHERE ID IN (SELECT post_id FROM wp_post2cat WHERE category_id = 2) AND comment_status = 'open' LIMIT 9001 (not tested)

thanks Haochi – I guess I’m really starting to get into custom query territory here, which is a little bit disappointing as I was hoping to be able to template some of this and be relatively confident that it’s going to work on future versions.

I guess I’ll just have to keep this stuff to a minimum so if I have to revisit it, at least it won’t be buckets of the stuff.

cheers.

for future reference… the function I’ve ended up using is as follows. I’ve called it get_tickets because I’m using this to display posts styled as trouble tickets (hence the relevance of the open/closed status):

function get_tickets($categories, $status='open', $limit=100) {
   global $wpdb;
   $sql = "SELECT * FROM $wpdb->posts
          LEFT JOIN $wpdb->term_relationships
          ON($wpdb->posts.ID=$wpdb->term_relationships.object_id)
          LEFT JOIN $wpdb->term_taxonomy
          ON($wpdb->term_relationships.term_taxonomy_id=$wpdb->term_taxonomy.term_taxonomy_id)
          WHERE $wpdb->term_taxonomy.term_id='$categories'
          AND $wpdb->term_taxonomy.taxonomy='category'
          AND $wpdb->posts.post_status='publish'
          AND $wpdb->posts.comment_status='$status'
          ORDER BY $wpdb->posts.post_date DESC
          LIMIT $limit;";
   $results = $wpdb->get_results($sql);
   if (!empty($results)) return $results;
   else return FALSE;
}

to then display the results, you would:

<?php
   $tickets = get_tickets($ticket_category_id, 'open');
   if ($tickets) foreach ($tickets as $post) :
   setup_postdata($post);
?>

   [regular loop stuff goes here]

<?php endforeach; ?>

obviously, you can adapt this for your own needs.