Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

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);
share|improve this question
add comment

4 Answers

up vote 3 down vote accepted

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

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.

share|improve this answer
    
Thanks for the reply Jessica, but that still results in the same problem. If the posts don't have a value in the custom_order field then they aren't displayed at ll. –  Ryan Jun 19 '12 at 23:44
    
@Ryan I see, I think I read the original question too quickly. Is there some reason why you don't want to use two loops? If that'd work for you, I can post some code.. –  jessica Jun 19 '12 at 23:57
    
I am not against using multiple loops, but I was just hoping to find a way to do it all within one. Thanks for the reply! –  Ryan Jun 20 '12 at 0:05
1  
@Ryan - sounds like you'll have to alter the orderby clause as well. you can order on multiple columns, probably do something like sort on meta_value is not null to put those at the front. dba stackexchange may be a good place to ask for help on that. –  Milo Jun 26 '12 at 21:35
1  
@jessica - I've actually been playing with both - The two loops solution seems to be working right now. –  Ryan Aug 24 '12 at 17:14
show 14 more comments

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

share|improve this answer
add comment

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());
    }
}
share|improve this answer
1  
Please just explain what your code is doing instead of ranting about the Query limitations you think WP has. –  kaiser Jul 26 '13 at 12:59
add comment

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 );
share|improve this answer
2  
You'll end up doing two queries, sorting them in PHP, and returning (possibly) way more posts than you actually want. You'll also probably break pagination. This seems like a very fragile approach for both performance and maintenance. –  Evan Jun 14 '12 at 22:37
1  
@Evan either I misunderstand the question, or you misunderstand my answer. –  Milo Jun 14 '12 at 23:12
    
Evan, what would you recommend doing instead? I'm going to try this solution for the time being and see if I can get it working. Thanks both! –  Ryan Jun 19 '12 at 14:45
1  
@Ryan - the important question here is the context within this will be used- if you want it to work like a normal category page with pagination then this solution won't work for you, you'll have to filter the individual query components and alter the SQL directly. –  Milo Jun 19 '12 at 14:52
    
@Milo - Thanks! I updated the original question with some more information about the final goal. –  Ryan Jun 19 '12 at 23:42
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.