Sorting: custom query with orderby meta_value_num THEN by title

3 Views

i try to run a custom post type query to match following criteria:
sort movies first by year in descending order,
after that (“inside” the year order) by title alphabetically.

desired output:
movie title A, 2006
movie title Z, 2006
…
movie title A, 1996
movie title Z, 1996

Read More

i use the following code: 

$wp_query = new WP_Query();
$wp_query->query( array(
'post_type' => 'movies', 
                      'distribution' => 'companyA', 
                      'meta_key' => 'year',
                      'orderby' => 'meta_value_num title',  
                      'order' => 'DESC', 
                      'posts_per_page' => -1, 
                      'post_status' => 'publish',
));

i tried several things but i can only get this “inverse” result:

movie title A, 1996
movie title Z, 1996
…
movie title A, 2006
movie title Z, 2006

if i change DESC, ASC it only changes the title sorting. but i need to apply it to the year and not to the title.

is it the right way to use two orderby values? or do i have to use a meta_query or custom SQL ?

thx in advance!

Here is the resulting SQL Query from $GLOBALS['wp_query']->request

SELECT wp_posts.*

FROM   wp_posts

       INNER JOIN wp_term_relationships

               ON ( wp_posts.id = wp_term_relationships.object_id )

       INNER JOIN wp_postmeta

               ON ( wp_posts.id = wp_postmeta.post_id )

WHERE  1 = 1

       AND ( wp_term_relationships.term_taxonomy_id IN ( 24 ) )

       AND wp_posts.post_type = ‘movies’

       AND ( wp_posts.post_status = ‘publish’ )

       AND ( wp_postmeta.meta_key = ‘year’ )

GROUP  BY wp_posts.id

ORDER  BY wp_postmeta.meta_value + 0,

          wp_posts.post_title DESC 

Post Views: 3

Related posts

2 comments

  1. This is very crude but should sort your posts by year (meta_value) and then by title. It does depend on how the query is setup so it will only work with the query below or with similar ones.

    function alter_order_wpse_103181($order,$qry) {
  remove_filter('posts_orderby','alter_order',1,2);
  $order = explode(',',$order);
  $order = implode( ' ASC,',$order);
  return $order;
}
add_filter('posts_orderby','alter_order_wpse_103181',1,2);

$q = new WP_Query();
$q->query( array(
  'post_type' => 'movies', 
  'distribution' => 'companyA', 
  'meta_key' => 'year',
  'orderby' => 'meta_value_num title',  
  'order' => 'ASC', 
  'posts_per_page' => -1, 
  'post_status' => 'publish',
));
var_dump($q->request);

  2. Your issue is not as much with orderby as with order. While orderby accepts multiple values and your usage seem ok, order only accepts ASC or DESC.

    After sanitizing order is appended to output of orderby processing. If I understand the logic right that means that out of multiple orderby parameters, order will apply to last one listed.

    Try reversing orderby to 'title meta_value_num' so title is sorted by default and order applies to year instead of title.

Comments are closed.