List Posts by Category for a Non-Zero, Non-NULL Custom Field Value?

2 comments

1. Hi @Brian Fidler:

   There are several ways to solve this problem. I picked one that may appear more complex but is easier on the theme code, and also self-contained and probably more performant than some other approaches (certainly more performant than doing repeated query_posts() for each category.)

   Basically I created a class called PostsByCategoryWithPrice where you call the static method query() to get a list of posts. It also has a helper method called get_category() to encapsulate the retrieval of the category information.

   It returns a list of posts ordered by category name so you can loop through them and echo an <h2>{$post_title}</h2> every time the category changes. Of course it filters out any without a price. And it uses the get_category_link() function to remove that complexity from your code.

   Here’s how it might look in one of your your theme template files (I didn’t have enough of the right data to test this code completely so the column counting may be off a bit, but I did test the query class that it calls and that should be good to go):

   $category = PostsByCategoryWithPrice::get_category(0);
   $posts = PostsByCategoryWithPrice::query();
   $column=0;
   foreach($posts as $post) {
     if ($column++>6)
       $column = 1;
     if ($category->id != $post->category_id) {
       $category = PostsByCategoryWithPrice::get_category($post); ?>
       <h2 style="clear:both;">
         <a href="<?php echo get_category_link($category->id); ?>">
           <?php echo($category->name); ?></a>
       </h2><?php
     }?>
     <div class="span-1 thumb20<?php echo ($count==6 ? ' last' : ''); ?>">
       <a class="thumb" href="<?php the_permalink(); ?>" rel="bookmark" 
          title="Link to <?php the_title(); ?>">
         <?php echo the_post_thumbnail( 'thumbnail' ); ?>
       </a>
     </div><?php
   }?>
   <hr class="gallery-grid" />
   

   Next is the class PostsByCategoryWithPrice1. It used four hooks to modify a WP_Query(): they are: 'posts_fields', 'posts_join', 'posts_where' and 'posts_orderby'. The class looks more complex than some people might be familiar with but it really isn’t that complex, it’s just there to encapsulate the hooks so they don’t affect any other part of your site.

   I had to use all the hooks and especially the four (4) joins because, while WP_Query() will do taxonomy and meta joins, it respectively only does so for when you are filtering by taxonomy and only when you have an exact match for meta_value, not one where NULL has to be considered (I recently requested that something be added to WordPress core to address the comparison with NULL and was told it was a good idea by a core team member, but it’s not here yet.)

   class PostsByCategoryWithPrice {
     static $hooks = array();
     private static function push_action($hook,$callable,$priority=10,$params=1) {
       self::$hooks[$hook] = $callable;
       add_action($hook,$callable,$priority,$params);
     }
     private static function pop_action($count=1) {
       for($i=$count; $i>0; $i--) {
         $hook = end(array_keys(self::$hooks));
         $callable = array_pop(self::$hooks);
         remove_action($hook,$callable);
       }
     }
     static function get_category($post=false) {
       if (!$post)
         $category = (object)array('id'=>false);
       else
         $category = (object)array(
           'id'    => $post->category_id,
           'name'  => $post->category_name,
           'slug'  => $post->category_slug,
           );
       return $category;
     }
     static function query() {
       self::push_action('posts_fields',array(__CLASS__,'posts_fields'));
       self::push_action('posts_join',array(__CLASS__,'posts_join'));
       self::push_action('posts_where',array(__CLASS__,'posts_where'));
       self::push_action('posts_orderby',array(__CLASS__,'posts_orderby'));
       $query = new WP_Query("posts_per_page=-1");
       self::pop_action(4);
       return $query->posts;
     }
     static function posts_fields($fields) {
       global $wpdb;
       $fields .= ",{$wpdb->terms}.term_id AS category_id,
                    {$wpdb->terms}.name AS category_name,
                    {$wpdb->terms}.slug AS category_slug";
       return $fields;
     }
     static function posts_join($join) {
       global $wpdb;
       $join .=<<<SQL
   INNER JOIN {$wpdb->postmeta} 
           ON {$wpdb->postmeta}.post_id={$wpdb->posts}.ID 
          AND {$wpdb->postmeta}.meta_key='price'
   INNER JOIN {$wpdb->term_relationships} 
           ON {$wpdb->term_relationships}.object_id={$wpdb->posts}.ID
   INNER JOIN {$wpdb->term_taxonomy} 
           ON {$wpdb->term_taxonomy}.term_taxonomy_id={$wpdb->term_relationships}.term_taxonomy_id
   INNER JOIN {$wpdb->terms} 
           ON {$wpdb->term_taxonomy}.term_id={$wpdb->terms}.term_id
   SQL;
       return $join;
     }
     static function posts_where($where) {
       global $wpdb;
       $where .="  AND {$wpdb->term_taxonomy}.taxonomy='category' 
                   AND IFNULL({$wpdb->postmeta}.meta_value,'0')!='0' ";
       return $where;
     }
     static function posts_orderby($orderby) {
       global $wpdb;
       $orderby = " {$wpdb->term_taxonomy}.name,
                    {$wpdb->posts}.post_title ASC ";
       return $orderby;
     }
   }
   

   Here is a self-contained example version of the above code that you can save to the root of your website (as /test.php maybe?) to test it out for your use-case. Once you get it working you can copy the class into your theme’s functions.php file and/or into a .php file of a plugin you are building, and use the template code in your theme in a “Page Template” file (or a custom post type template file) where you want to display these post thumbnails.

   You can download it from Gist:

   • posts-by-category-with-price.php

   Log in to Reply

2. Do not use query_posts() for this. Always use get_posts() or WP_Query for secondary loops.

   Try something like this:

   $categories = get_categories();
   
   foreach ( $categories as $category ) {
   
       $posts = get_posts( array(
       'cat' => $category->cat_ID,
       'posts_per_page' => '-1',
       'orderby' => 'title',
       'order' => 'ASC',
       'meta_key' => 'price',
       'meta_compare' => '>',
       'meta_value' => '0'
       ) );
   
   
       if( !count( $posts ) )
            continue;
   
       // else we have some posts in this category
   }
   

   Log in to Reply