22

I'm working on a custom taxonomy term page template where we want the items that are connected to the term sorted by a publication date (custom date field) - and if there are multiple items on the same day (formatted like YYYY-MM-DD) to then sort those by title, and finally sort by title if the custom field has not been filled out (older items).

So, I tried it hundred different ways with a WP_query and it does return most of the results as I want them - but in this case it's only returning the items that have the meta_key of publication_date. All other items are being ignored and not displayed. I tried a meta_query using a relation of "or" and compared the publication_date as EXISTS and NOT EXISTS, but that returned 0 results for me.

Also, the site is running 3.5.2 still and they do not want to upgrade.

Here is my most recent query that gets me the posts that have the publication_date custom field displayed in the correct order:

$term = get_queried_object(); // find the term of the taxonomy page we are on
$wp_query = new WP_Query( array(
'post_type' => 'resource',
'tax_query' => array(
    array(
        'taxonomy' => 'resource_types',
        'field' => 'slug',
        'terms' => $term->name,
    )), 

'meta_key' => 'publication_date',
'orderby' => 'meta_value_num',
'order' => 'DESC',
'paged' => $paged,
'posts_per_page' => '10',
));

I also tried using wpdb and running a SQL query, but I really am not sure how to accomplish what I want doing that. If someone could help me out that would be awesome!

Thanks in advance.

  • Surprised the meta_query approach didn't work, but then again you can't orderby meta value with a meta_query without having the meta_key set. – sanchothefat Dec 17 '13 at 16:54
  • I think that is the problem I am having. I did finally get a meta query working: 'meta_query' => array( 'relation' => 'OR', array( //check to see if date has been filled out 'key' => 'publication_date', 'compare' => '!=', 'value' => date('Y-m-d'), ), array( //if no date has been added show these posts too 'key' => 'publication_date', 'value' => date('Y-m-d'), 'compare' => 'NOT EXISTS' ) ), but the ordering isn't working :\ – CSSgirl Dec 17 '13 at 17:01
  • yep, ordering relies upon meta_key being set outside of the tax_query unfortunately. My answer below might help though. – sanchothefat Dec 18 '13 at 16:09
19

Thank you everyone for your help!

In the end the query below got me the results I desired - which was to show and sort the posts by a custom field of "publication_date" first - sorting by the date, and if there were multiple of the same date (say, 4 marked June 2013), it would sort those by title. Then, after it runs through all the posts that have the Publication Date filled in it will loop through again the remaining posts, alphabetically by title.

This gets me the results set in the same query, and keeps my pagination:

$term = get_queried_object();
the_post();
$wp_query = new WP_Query( array(
'post_type' => 'resource',
    'tax_query' => array(
        array(
            'taxonomy' => 'resource_types',
            'field' => 'slug',
            'terms' => $term->name,
        )),
 'meta_query' => array(
       'relation' => 'OR',
        array( //check to see if date has been filled out
                'key' => 'publication_date',
                'compare' => '=',
                'value' => date('Y-m-d')
            ),
          array( //if no date has been added show these posts too
                'key' => 'publication_date',
                'value' => date('Y-m-d'),
                'compare' => 'NOT EXISTS'
            )
        ),
'meta_key' => 'publication_date',
'orderby' => 'meta_value title',
'order' => 'ASC',
'paged' => $paged,
'posts_per_page' => '10',
));
  • 1
    Nice. I never thought to run two meta_query on the same key! – GhostToast Dec 19 '13 at 16:13
  • 5
    For me (using WordPress 4.1.1), if I set meta_key it automatically doesn't include it even with NOT EXISTS. I really hope I'm doing something wrong. – Ryan Taylor Mar 24 '15 at 22:59
  • 1
    @RyanTaylor same here - meta_key must not be set in the query for this to work, but it does appear to order correctly by meta value even when meta key is not set. – jammypeach Jan 21 '16 at 10:50
  • 2
    As above comments, for WP 4.1+ remove or comment out 'meta_key' => 'publication_date',. – MikeiLL Jul 14 '16 at 5:35
7

Few years later, the code posted by CSSGirl wasn't working for me because there were some posts that didn't have the meta key or the meta key was empty so this is what I had to do to have all the posts ordered by date and show the ones with a meta key value display first:

$args          = array(
'post_type'   => $type,
'post_status' => 'publish',
'nopaging'    => TRUE,
'meta_query'  => array(
    'relation' => 'OR',
    array(
        'key'     => $meta_key,
        'compare' => 'NOT EXISTS',
    ),
    array(
        'relation' => 'OR',
        array(
            'key'   => $meta_key,
            'value' => 'on',
        ),
        array(
            'key'     => $meta_key,
            'value'   => 'on',
            'compare' => '!=',
        ),
    ),
),
'orderby'     => array( 'meta_value' => 'DESC', 'date' => 'DESC' ),
);
1

I think you'd need to do 2 separate loops. You can capture all the posts found in the first loop and exclude them from the secondary loop easily enough:

$found_posts = array();
while($loop->have_posts()): $loop->the_post();
    // loop stuff
    $found_posts[] = get_the_id();
endwhile;

wp_reset_query();

$args = array(
    // other args
    'post__not_in' => $found_posts,
);

Then run your second loop.

  • Trying this now, thanks. Will let you know if it works! – CSSgirl Dec 17 '13 at 17:00
  • 1
    this worked - but it broke the pagination - any idea how to get this to work? Here's what it looks like now: echo paginate_links( array( 'base' => str_replace( $big, '%#%', esc_url( get_pagenum_link( $big ) ) ), 'format' => '?page=%#%', 'current' => max( 1, get_query_var('paged') ), 'total' => $publication_query->max_num_pages, 'prev_text' => __('Previous |'), 'next_text' => __('| Next'), ) ); – CSSgirl Dec 17 '13 at 17:11
  • Hmm. Not that I can think of. – GhostToast Dec 17 '13 at 17:18
1

Is there any reason you couldn't enforce the publication_date meta key to exist for every post just with an empty value?

So in your save_post action you would add/update the meta key whether the $_POST value is empty or not.

You would have to run an update script to loop over your older posts and add the key with an empty value eg:

add_action( 'admin_init', 'update_old_posts' );
function update_old_posts() {
    if ( ! isset( $_GET[ 'update_old_posts' ] ) )
         return;

    foreach( get_posts() as $post ) {
        if ( false === get_post_meta( $post->ID, 'publication_date', true ) ) {
             update_post_meta( $post->ID, 'publication_date', '' );
             echo "Updated {$post->post_title} <br />";
        }
    }

    die;
}

Run it by browsing to http://example.com/wp-admin/?update_old_posts

Then you can use the same query as you have. You may want to add an extra filter to enable you to order by different columns in different directions, it would make to sense to me to sort by date in descending order and title in ascending order.

add_filter( 'posts_orderby', 'multicolumn_orderby', 10, 2 );
function multicolumn_orderby( $orderby, $query ) {
    global $wpdb;

    // check it's the right query
    if ( $query->get( 'meta_key' ) == 'publication_date' ) {
         $orderby = "$wpdb->postmeta.meta_value+0 DESC, $wpdb->posts.post_title ASC";
    }

    return $orderby;
}
  • Hmm, I didn't think of that. I'm going to give that a try and see how it goes, thanks! – CSSgirl Dec 17 '13 at 18:24
0

I created a custom where clause. I tested it using $wp_query->request right before my main loop, I don't really know SQL that well, but this seemed to get things working.

add_action('pre_get_posts', 'add_trending_sort', 11, 1);
function add_trending_sort($query){
  if(!$query->is_main_query())
    return;

  //Overwrite query arguments
  $query->set('meta_query', array(
    array(
      'key' => 'TRENDING',
      //'value' => 'asdfasdf',//may need a value for older versions of WordPress
      'compare' => 'NOT EXISTS',
    )
  ));
  $query->set('orderby', 'meta_value_num date');
  $query->set('order', 'DESC');
}

add_filter('posts_where', 'add_trending_where');
function add_trending_where($where = ''){
  global $wpdb, $wp_query;
  if(!$wp_query->is_main_query())//Not sure if this really works.  Should be OK
    return $where;

  $where .= " OR ( $wpdb->postmeta.meta_key = 'TRENDING' )";

  // Don't run this twice
  remove_filter('posts_where', 'add_trending_where');

  return $where;
}

Alternatively, you could set compare to 'EXISTS' and change the line in add_trending_where to $where .= " OR ($wpdb->postmeta.post_id IS NULL)";. Then you'd only have to change the value of the key in one place. Again, echo $wp_query->request and play around if you want understand this better or tweak it.

EDIT: I just noticed this does not work if meta_key is set on the query. You could use $query->set('meta_key', NULL); if you have to.

EDIT 2: I got this working with the method above. For some reason it wasn't at first (maybe meta_key was set... I don't know).

add_action('pre_get_posts', 'add_trending_sort', 11, 1);
function add_trending_sort($query){
  // Bail if not the main "hidden" query, as opposed to a 'new WP_Query()' call
  if(!$query->is_main_query())
    return;

  // Set meta_query to get shares for orderby, and also get non-shared content.
  $query->set('meta_query', array(
    'relation' => 'OR',
    array(
      'key' => 'TRENDING',
      'compare' => 'NOT EXISTS',
    ),
    array(
      'key' => 'TRENDING',
      'compare' => 'EXISTS',
    )
  ));
  //$query->set('meta_key', NULL);
  $query->set('orderby', array('meta_value_num' => 'DESC', 'date' => 'DESC'));
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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