Sorting: custom query with orderby meta_value_num THEN by title

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

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

Contents hide

Answers:

Method 1

Method 2

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

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

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

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating