Order posts by custom field and if custom field is empty return remaining posts

6 comments

1. When you specify a meta_key, query_posts() does an INNER JOIN between the wp_posts and wp_postmeta table. That means that any posts that don’t have any meta value for the key you specified won’t ever be returned in that query.

   In order to do what you need, you should use the same query you posted in your question, but change orderby=meta_value to orderby=meta_value_num. Then you can filter ‘get_meta_sql’ to make the join return all posts. Add the following to your functions.php:

   <?php
   function wpse_55791_custom_order($clauses)
   {
       global $wp_query;
   
       // check for order by custom_order
       if ($wp_query->get('meta_key') == 'custom_order' && $wp_query->get('orderby') == 'meta_value_num')
       {
           // change the inner join to a left join, 
           // and change the where so it is applied to the join, not the results of the query
           $clauses['join'] = str_replace('INNER JOIN', 'LEFT JOIN', $clauses['join']).$clauses['where'];
           $clauses['where'] = '';
       }
       return $clauses;
   }
   add_filter('get_meta_sql', 'wpse_55791_custom_order', 10, 1);
   ?>
   

   EDIT: To fix the ordering, try adding this along with the above:

   <?php
   function wpse_55791_custom_orderby($orderby)
   {
       global $wp_query, $wpdb;
   
       // check for order by custom_order
       if ($wp_query->get('meta_key') == 'custom_order' && $wp_query->get('orderby') == 'meta_value_num')
       {
           $orderby = "{$wpdb->postmeta}.meta_value='', ".$orderby;
       }
       return $orderby;
   }
   add_filter('posts_orderby', 'wpse_55791_custom_orderby', 10, 1);
   ?>
   

   EDIT TWO – 2 loops:

   Here’s how I’d do it:

   $paged = (get_query_var('paged')) ? get_query_var('paged') : 1; 
   $ordered_posts = new WP_Query(array(
       'paged' => $paged,
       'cat' => 7,
       'posts_per_page' => 24,
       'meta_key' => 'custom_order',
       'orderby' => 'meta_value_num',
       'order' => 'ASC',
   ));
   
   $unordered = new WP_Query(array(
       'cat' => 7,
       'paged' => $paged,
       'posts_per_page' => 24 - $ordered_posts->post_count,
   ));
   
   if ($ordered_posts->have_posts()) :
       while ($ordered_posts->have_posts()) : $ordered_posts->the_post();
       // loop 1
       endwhile;
   endif;
   
   if ($unordered_posts->have_posts()) :
       while ($unordered_posts->have_posts()) : $unordered_posts->the_post();
       // loop 2
       endwhile;
   endif;
   

   Note that if you think there are ever going to be more than 24 ordered posts, the paged variable will be wrong for the unordered posts; you might need to set a global variable to keep track of how many ordered / unordered posts have been displayed so far and use that to calculate separate $paged values for each type.

   Log in to Reply

2. You could put the post IDs in an array and then query for all posts excluding those IDs:

   $post_ids = array();
   foreach( $wp_query->posts as $post ):
       $post_ids[] = $post->ID;
   endforeach;
   
   $args = array(
       'posts_per_page' => -1,
       'post__not_in' => $post_ids
   );
   $remaining = new WP_Query( $args );
   

   Log in to Reply

3. I solved this in a very easy way, using an array for the ordering, as below:

   $posts = get_posts(array(
                       'orderby' => array(
                           'meta_key' => 'meta_key', /* your meta key here */
                           'meta_value' => 'meta_value_num',
                           'order' => 'DESC',
                       )
               ));   
   

   That should fix it 🙂

   Log in to Reply

4. This is a good workaround even if I dont like it at all. It’s again the limitation of the query api of wordpress. I loved to do it with mysql in the old ages.. but messing with WP is sometimes..you know

   //get only ordered posts
   $args = array(
       'post_type'=>'supporters',
       'order' => 'ASC',
       'orderby' => 'meta_value_num',
       'meta_key' => 'wpcf-sorting'
   );
   $cat_posts = new WP_Query($args);
   
   //collect ordered post ids
   $post_ids = array();
   foreach( $cat_posts->posts as $post ):
       $post_ids[] = $post->ID;
   endforeach;
   
   
   //get only unordered posts
   $args = array(
       'post_type'=>'supporters',
       'posts_per_page' => -1,
       'post__not_in' => $post_ids
   );
   $cat_posts_unordered = new WP_Query($args);
   
   
   if ($cat_posts->have_posts() || $cat_posts_unordered->have_posts()) {   
       while ($cat_posts->have_posts()) {
           $cat_posts->the_post();
   
           get_template_part( 'content-supporters', get_post_format());
       }
       while ($cat_posts_unordered->have_posts()) {
           $cat_posts_unordered->the_post();
   
           get_template_part( 'content-supporters', get_post_format());
       }
   }
   

   Log in to Reply

5. In terms of easiness and as my solution is for the admin-panel it doesn’t focus on efficiency. What I did is loading all posts and then sorting it using php.

   function MY_PLUGIN_get_ordered_pages_by_cat_id($cat_id) {
       $all_pages = get_posts(
                   array(
                       'post_type' => 'page',
                       'cat' => $cat_id,
                       'order' => 'ASC'
                   )
               );
   
       $pages      = [];
       $page_len   = count($all_pages);
       foreach($all_pages as $page):
           $positon        = @get_post_meta($cat_id, '_page_position', true);
           $a_pos          = !empty($positon) ? $positon : $page_len++;
           $pages[$a_pos]  = $page;
       endforeach;
       sort($pages);
       return $pages;
   }
   

   Maybe its helpful to someone. 🙂

   Log in to Reply

6. Even if the question is very old, it seems not to be answered yet (see Edit 2 in the above question).
   I wasn’t happy with the previous solutions so I used nested meta queries to solve the problem.

       function sort_posts_by_custom_field( $query ) {
           $query->set('meta_query', array(
               'relation' => 'OR',
               'custom_field_clause' => array(
                   'key' => 'custom_field',
                   'value' => '0',
                   'compare' => '>',
                   'type' => 'DECIMAL'
               ),
               'no_custom_field_clause' => array(
                   'relation' => 'OR',
                   array(
                       'key' => 'custom_field',
                       'compare' => 'NOT EXISTS'
                   ),
                   array(
                       'key' => 'custom_field',
                       'value' => null,
                       'compare' => '='
                   )
               )
           ));
           $query->set('orderby', array('custom_field_clause' => 'ASC'));
           return $query;
       }
       add_action('pre_get_posts', 'sort_posts_by_custom_field');
   

   In Words: Before querying posts, query those posts, that have custom_field set and those, that don’t have custom_field set (or set to null). (Query both, elsewise one of them will be ignored.) Then sort by custom_field ascending.

   Log in to Reply