Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

I've got two custom fields for event posts: event-start and event-end. When I'm querying for current events, I'd like to show any post where the event-start OR the event-end is greater than or equal to today's date and I'd like to order them by event-start (not the default post_date).

I've created a query with the following parameters:

$args = array(
  'post_type' => 'post',
  'posts_per_page' => $number,          
  'meta_query' => array(
    'relation' => 'OR',
    array(
      'key' => 'event-start',
      'value' => $today_is,
      'type' => 'NUMERIC',
      'compare' => '>='
    ),
    array(
      'key' => 'event-end',
      'value' => $today_is,
      'type' => 'NUMERIC',
      'compare' => '>='
    )
  ),
  'orderby' => 'meta_value',
  'order' => 'ASC',
);

The query gets the correct posts, but the problem is I can't get the orderby to work. This set of args run the following query:

SELECT SQL_CALC_FOUND_ROWS wp_18_posts.ID FROM wp_18_posts INNER JOIN wp_18_postmeta ON (wp_18_posts.ID = wp_18_postmeta.post_id)
INNER JOIN wp_18_postmeta AS mt1 ON (wp_18_posts.ID = mt1.post_id) WHERE 1=1 AND wp_18_posts.post_type = 'post' AND (wp_18_posts.post_status = 'publish' OR wp_18_posts.post_status = 'private') AND ( (wp_18_postmeta.meta_key = 'event-start' AND CAST(wp_18_postmeta.meta_value AS SIGNED) >= '1344289896')
OR (mt1.meta_key = 'event-end' AND CAST(mt1.meta_value AS SIGNED) >= '1344289896') ) GROUP BY wp_18_posts.ID **ORDER BY wp_18_posts.post_date** ASC LIMIT 0, 3

Notice the ORDER BY clause is still set to post_date. If I change the query args to use the old-style of meta_key as well as orderby meta_value along with the new-style meta_query like this:

$args = array(
  'post_type' => 'post',
  'posts_per_page' => $number,      
  'meta_query' => array(
    'relation' => 'OR',
    array(
      'key' => 'event-start',
      'value' => $today_is,
      'type' => 'NUMERIC',
      'compare' => '>='
    ),
    array(
      'key' => 'event-end',
      'value' => $today_is,
      'type' => 'NUMERIC',
      'compare' => '>='
    )
  ),
  'orderby' => 'meta_value',
  'meta_key' => 'event-start',
  'order' => 'ASC',
);

I end up with the following query:

SELECT SQL_CALC_FOUND_ROWS wp_18_posts.ID FROM wp_18_posts INNER JOIN wp_18_postmeta ON (wp_18_posts.ID = wp_18_postmeta.post_id)
INNER JOIN wp_18_postmeta AS mt1 ON (wp_18_posts.ID = mt1.post_id)
INNER JOIN wp_18_postmeta AS mt2 ON (wp_18_posts.ID = mt2.post_id) WHERE 1=1 AND wp_18_posts.post_type = 'post' AND (wp_18_posts.post_status = 'publish' OR wp_18_posts.post_status = 'private') AND (wp_18_postmeta.meta_key = 'event-start'
OR (mt1.meta_key = 'event-start' AND CAST(mt1.meta_value AS SIGNED) >= '1344289518')
OR (mt2.meta_key = 'event-end' AND CAST(mt2.meta_value AS SIGNED) >= '1344289518') ) GROUP BY wp_18_posts.ID ORDER BY wp_18_postmeta.meta_value ASC LIMIT 0, 3

Which pulls all posts with an event-start meta key regardless of the date set for that key.

Any ideas how to setup a complex custom field query and orderby the event-start value would be great appreciated.

share|improve this question

2 Answers 2

If the value of your custom fields is numeric, you could try ordering with meta_value_num. Also note that both meta_value and meta_value_num require meta_key in the query as described here.

$args = array(
  'post_type' => 'post',
  'posts_per_page' => $number,          
  'meta_query' => array(
    'relation' => 'OR',
    array(
      'meta_key' => 'event-start',
      'value' => $today_is,
      'type' => 'NUMERIC',
      'compare' => '>='
    ),
    array(
      'meta_key' => 'event-end',
      'value' => $today_is,
      'type' => 'NUMERIC',
      'compare' => '>='
    )
  ),
  'orderby' => 'meta_value_num',
  'order' => 'ASC',
);
share|improve this answer
    
I've used this method on a current project and it works well. Just be sure to store dates as YYYYMMDD:HHMMSS from the get-go otherwise you will run into problems down the line. –  neoian Apr 25 at 20:18
    
@unifiedac When I run the query as you've created it, I end up with a query which ignores the meta keys all together and just returns any post with any meta value that matches the timestamp. I want to return posts that have only the specific meta key(s) with the values that match my needs. From all the documentation I can see, the complex query arguments should look use the 'key' => '', 'value' => '' format, not 'meta_key' => '', 'value' => ''. –  DaveE Apr 28 at 15:29
up vote 0 down vote accepted

Here's the best solution that I've found:

$args = array(
    'post_type'      => 'post',
    'posts_per_page' => $number,
    'meta_key'       => 'event-start',
    'meta_value'     => $today_is,
    'meta_query'     => array(
        'relation' => 'OR',
        array(
            'key'     => 'event-start',
            'value'   => $today_is,
            'type'    => 'NUMERIC',
            'compare' => '>=',
        ),
        array(
            'key'     => 'event-end',
            'value'   => $today_is,
            'type'    => 'NUMERIC',
            'compare' => '>=',
        )
    ),
    'orderby' => 'meta_value',
    'order'   => 'ASC',
);

It uses the same complex meta_query, but I added a separate meta_key and meta_value in order to trigger the meta_value orderby.

If I recall correctly, at the time I wrote this post, I had tried this solution, but it didn't work. I also believe I came across a Trac ticket in WordPress Core related to this issue, but for the life of me can't find it now.

Bottom line: at the time of the original post, I think there was a WordPress bug that prevented this from working, but it works now.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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