Trying to perform complex custom field query with order by set to field value

Question

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.

Answer 1

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',
);

Answer 2

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.