Orderby custom field meta value ASC and then by date DESC

0 Views

I need to allow post ordering in certain categories. One of the sorting options is a custom field meta value.
First problem is that not all posts have that meta assigned and i need to order these by date (default).

Another problem is that meta field should use ASC order and the rest DESC.

Read More

My code so far

In Template File: 

$request = $_REQUEST;
global $query_string;
parse_str( $query_string, $qs_arr );

switch ( $request['sort'] ) {

    case 'title':
        $qs_arr['orderby'] = 'title';
        $qs_arr['order'] = 'ASC';
        break;
    case 'publisher':
        $qs_arr['meta_key'] = 'discography_publisher';
        $qs_arr['orderby'] = 'meta_value';

        add_filter( 'posts_orderby', 'sort_posts_orderby' );
        add_filter( 'get_meta_sql', 'sort_get_meta_sql' );
        add_filter( 'posts_where' , 'sort_posts_where' );
        break;
}

if ( isset( $request['sort'] ) ) { query_posts( $qs_arr ); }

functions.php 

function sort_get_meta_sql( $meta_sql ) {
    $meta_sql['join'] = " LEFT JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id AND wp_postmeta.meta_key = 'discography_publisher') ";
    return $meta_sql;
}

function sort_posts_where( $where ) {
    //here i remove the last statement "wp_postmeta.meta_key = 'discography_publisher' " so query return all posts in category 
    $explode = explode( 'AND', $where );
    array_pop( $explode );
    return implode( 'AND', $explode );
}



function sort_posts_orderby( $orderby ) {
    $orderby = 'COALESCE(wp_postmeta.meta_value, wp_posts.post_date) DESC';
    return $orderby;
}
Post Views: 0

Related posts

Leave a Reply

2 comments

  1. Assuming the rest works, why not order the normal way:

    function sort_posts_orderby( $orderby ) {
    $orderby = 'wp_postmeta.meta_value ASC, wp_posts.post_date DESC';
    return $orderby;
}

  2. I had something similar, where I ordered CPTs with an ‘order’ field, but not all of them had an assignement value, my order by :

     //SELECT
 //FROM
 //WHERE
 ORDER BY
      wp_postmeta.meta_value = '' ASC,
      wp_postmeta.meta_key ASC,
      wp_posts.post_date DESC

    Results in ordering the posts with order field first, then orders the posts with no value by post date in descending order.