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

Question

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

Answer 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.

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

Answer 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 :)

Answer 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());
    }
}

Answer 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. :)

Answer 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.