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

by

I have an archive page ordered by a numeric value in a custom field. This returns the ordered posts correctly, but does not display posts that don’t have a custom field. 

$paged = (get_query_var('paged')) ? get_query_var('paged') : 1; 
query_posts("paged=$paged&cat=7&posts_per_page=24&meta_key=custom_order&orderby=meta_value_num&order=ASC");

What is the best way to return the ordered posts correctly, followed by any posts that do not have a custom field value associated with them?

Edit: Just to elaborate — The final result that I want to accomplish is a category archive page that has specific posts show up first, followed by the rest. Pretty much like sticky posts, but only for a specific category archive.

Edit Two: I am trying Jessica’s suggestion, and I think we’re almost there.

The problem is that now if I set to order=ASC then all of the posts that have the custom field filled, show up after the posts that don’t have a value associated. If I set order=DESC then the posts that have a custom field value are returned first, but in reverse. So, it would appear 4, 3, 2, 1, and then the rest of the posts with no value associated. How can I correct the order, so it shows 1, 2, 3, 4, and then the rest of the posts with no value in the custom_order field?

I have added the following to my functions.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 outer 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 OUTER JOIN', $clauses['join']).$clauses['where'];
        $clauses['where'] = '';
    }
    return $clauses;
}
add_filter('get_meta_sql', 'wpse_55791_custom_order', 10, 1);
function wpse_55791_custom_orderby($orderby)
{
    global $wp_query;

    // 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);

6 Answers
6

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.

Leave a Comment