1

I have a custom post type ”events” with meta fields ”startdate” and ”enddate”. Archive query already uses meta query to show only upcoming or ongoing events, but in addition I need to order by both meta fields. I can't figure out what the meta query should be, so it works for both uses. Or am I doing something wrong?

This is part of the query, with the simple orderby that I need to replace. Anyone got any ideas how to add ”...orderby startdate, enddate” to this?

$meta_query = array(
    'relation' => 'OR',
    'enddate' => array (
        'key' => 'enddate',
        'value' => date( 'Y-m-d' ),
        'compare' => '>='
    ),
    'startdate' => array (
        'key' => 'startdate',
        'value' => date( 'Y-m-d' ),
        'compare' => '>=',
    )
);
$query->set('meta_query',$meta_query);
$query->set('orderby', 'meta_value');   
$query->set('meta_key', 'startdate');
  • Welcome to WPSE! There may need to be some more context to your question. One thing that I would question is the format of the data. You'll have a hard time with querying (if >=) and sorting as a date because meta data is a string - it's never formatted as a date. One way around that is to store post meta that are dates using Unix epoch time (because that can be sorted as a string). Just a suggestion to look into. – butlerblog Nov 24 '18 at 0:45
  • Thanks for your feedback. The format is yyyy-mm-dd which might not be the best way to store dates, but it worked fine before migrating the data into Wordpress. – Petri V. Nov 24 '18 at 9:34
0

You've already named the clauses in your meta query, so you can reference these directly in the orderby argument:

$query->set(
    'orderby',
    array(
        'enddate' => 'DESC',
        'startdate' => 'DESC'
    )
);
  • This doesn't work as expected. I've printed out the query, but I'm not a database expert: SELECT posts.* FROM posts INNER JOIN postmeta ON ( posts.ID = postmeta.post_id ) WHERE 1=1 AND ( ( ( postmeta.meta_key = 'startdate' AND postmeta.meta_value >= '2018-11-23' ) OR ( postmeta.meta_key = 'enddate' AND postmeta.meta_value >= '2018-11-23' ) ) ) AND posts.post_type = 'event' GROUP BY posts.ID ORDER BY postmeta.meta_value ASC, CAST(postmeta.meta_value AS CHAR) ASC – Petri V. Nov 23 '18 at 23:42
  • I'm trying to achieve order where the events are ordered first by startdate, and when it matches with another, the event with the earlier (or empty) enddate would come first. – Petri V. Nov 23 '18 at 23:49
  • With this orderby, matching startdates are messed up. Additionally, all the startdates are not in order either. Seems like black magic to me, compared to a simple clause in good old mysql :) – Petri V. Nov 23 '18 at 23:56
  • ah, because your relationship is an OR, it can match posts that have one key or the other, but not necessarily both. I guess you have to make both keys required to order by both. – Milo Nov 24 '18 at 1:39

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.