8

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 

2 Answers 2

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
  • it works! thank you very much. i changed ASC to DESC though in the alter_order function to sort A to Z.
    – kyii
    Jun 17, 2013 at 7:44
  • ASC should short A-Z; DESC should sort Z-A.
    – s_ha_dum
    Jun 17, 2013 at 12:44
1

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.

4
  • thank you rarst. but unfortunately if i reverse the values i get a sort order just A-Z ignoring meta_value_num (guessing from the fact that it is the same order regardless if ORDER is DESC or ASC)
    – kyii
    Jun 16, 2013 at 22:28
  • @kyii I am not sure what you mean by "same order regardless" ?
    – Rarst
    Jun 16, 2013 at 23:05
  • sorry. if i reverse as you suggested i get an A-Z order of the posts, which stays the same although i change the order => parameter in the query form ASC to DESC and back. so i guess the meta_value_num is not applied. so your interesting point about the logic of the interplay of ORDER and ORDERBY is maybe somehow different? or i am doing it wrong?
    – kyii
    Jun 16, 2013 at 23:19
  • @kyii not sure... I would try to dump resulting query with different combinations. However I think you would need to modify SQL in the end, this logic is too restrictive for more complex sorts.
    – Rarst
    Jun 16, 2013 at 23:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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