Wp Cats N Posts In A Tree

post_id: 63 / post_date: 2011-07-10

I believe a sitemap should show posts under categories, and maintain the category hierarchy.

wp_list_categories cannot be asked to include posts.
And if you follow this suggestion and try and use get_posts, you will get a nasty surprise; it will get all posts in all sub categories of the one you want. and this WILL NOT DO!

I found that you need this query and that you can inherit the category walker and pass that to the wp_list_categories method. Am impressed!

select object_id, p.post_status from wp_term_relationships r
join wp_posts p on r.object_id = p.ID
 where p.post_status = 'publish' and  r.term_taxonomy_id =
 (SELECT term_taxonomy_id FROM wp_term_taxonomy WHERE taxonomy = 'category' and term_id = 4)

So to hook the walker's end_el, we add

//USAGE:
//wp_list_categories( array( 'title_li' => '', 'echo' => false , 'walker' => new PCWalker() ) ) .

class PCWalker extends Walker_Category {

  function end_el(&$output, $page, $depth, $args) {
      global $wpdb;
      $output .= "called with: " . $page->term_id;
      $posts = $wpdb->get_results("select object_id as ID from wp_term_relationships r "
        . "join wp_posts p on r.object_id = p.ID where p.post_status = 'publish' and r.term_taxonomy_id = "
        . "(SELECT term_taxonomy_id FROM wp_term_taxonomy WHERE taxonomy = 'category' and term_id = " . $page->term_id . ")");
      if($posts) :
        $output .= '<ul class="posts">'; foreach($posts as $post) { $output .= '
	<li>'; $output .= '<a title="link to ' . get_the_title($post->ID) . '" href="' . get_permalink($post->ID) . '">' . get_the_title($post->ID) . '</a>'; $output .= '</li>
'; } $output .= '</ul>
';
      endif;
      parent::end_el(&$output, $page, $depth, $args);
    }
}