querying on custom meta fields and sorting them by custom meta

Question

I have event posts with multiple custom date and time fields. To pull all the events happening this week, I am querying on the different date fields. Here is my code:

$today=date("Y-m-d");
$nextweek= date("Y-m-d", strtotime("+7 days"));

$args2 = array(

'post_type' => 'post',
'meta_query' => array(
'relation' => 'OR',

array(
'key' => 'date2',
'value' => array( $today, $nextweek ),
'compare' => 'BETWEEN'
),
array(
'key' => 'date3',
'value' => array( $today, $nextweek ),
'compare' => 'BETWEEN'
),
array(
'key' => 'date1',
'value' => array( $today, $nextweek ),
'compare' => 'BETWEEN'

)
)
);
$query2 = new WP_Query( $args2 ); ?>

When I put var_dump($query2->request) after this, I get the following string:

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID 
FROM wp_posts 
INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) 
INNER JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id) 
INNER JOIN wp_postmeta AS mt2 ON (wp_posts.ID = mt2.post_id) 
WHERE 1=1 AND wp_posts.post_type = 'post' 
AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') 
AND ( 
  (wp_postmeta.meta_key = 'date2' AND CAST(wp_postmeta.meta_value AS CHAR) BETWEEN '2013-05-21' AND '2013-05-28') 
  OR (mt1.meta_key = 'date3' AND CAST(mt1.meta_value AS CHAR) BETWEEN '2013-05-21' AND '2013-05-28') 
  OR (mt2.meta_key = 'date1' AND CAST(mt2.meta_value AS CHAR) BETWEEN '2013-05-21' AND '2013-05-28') 
) 
GROUP BY wp_posts.ID 
ORDER BY wp_posts.post_date DESC LIMIT 0, 10

This is pulling the correct posts, but now I want to sort the results on the date1 field. I tried this and it isn't working. I end up with all of the events posted, and not sorted either. I've also tried the 'meta_value' for orderby without luck. The values in the date1 field are in date format YYYY-MM-DD Any help would be appreciated.

$args2 = array(
'order' => 'ASC',
'meta_key' => 'date1',
'orderby' => 'meta_value_num',
'post_type' => 'post',
'meta_query' => array(
'relation' => 'OR',

array(
'key' => 'date2',
'value' => array( $today, $nextweek ),
'compare' => 'BETWEEN'
),
array(
'key' => 'date3',
'value' => array( $today, $nextweek ),
'compare' => 'BETWEEN'
),
array(
'key' => 'date1',
'value' => array( $today, $nextweek ),
'compare' => 'BETWEEN',

)
)

);

$query2 = new WP_Query( $args2 ); ?>

When I put var_dump($query2->request) after this I get:

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID 
FROM wp_posts 
INNER JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id 
INNER JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id) 
INNER JOIN wp_postmeta AS mt2 ON (wp_posts.ID = mt2.post_id) 
INNER JOIN wp_postmeta AS mt3 ON (wp_posts.ID = mt3.post_id) 
WHERE 1=1 
AND wp_posts.post_type = 'post' 
AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') 
AND (wp_postmeta.meta_key = 'date1' 
  OR (mt1.meta_key = 'date2' AND CAST(mt1.meta_value AS CHAR) BETWEEN '2013-05-21' AND '2013-05-28') 
  OR (mt2.meta_key = 'date3' AND CAST(mt2.meta_value AS CHAR) BETWEEN '2013-05-21' AND '2013-05-28') 
  OR (mt3.meta_key = 'date1' AND CAST(mt3.meta_value AS CHAR) BETWEEN '2013-05-21' AND '2013-05-28') 
) 
GROUP BY wp_posts.ID 
ORDER BY wp_postmeta.meta_value+0 ASC LIMIT 0, 10 

Answer 1

Your meta fields are JOINing as mt* aliases in that query. Look for the lines like:

INNER JOIN wp_postmeta AS mt1 ON ...

But the ORDER BY parameter is just wp_postmeta.meta_value, which doesn't actually make all that much sense. If that isn't a bug, I call it bad design or very limited design, but moving on.

To specifically target one of your meta values, the first one, you'd need that ORDER BY to be...

ORDER BY mt1.meta_value

You don't want the meta_value_num, by the way, because you don't have numbers. There are dashes in there. MySQL will interpret the date '2013-12-15' + 0 as just '2013'. Put SELECT ('2013-12-15')+0 into PhpMyAdmin's SQL panel and run the query.

To do this with the data you have you will need to create a filter for your query. Add a parameter to your query...

$args2 = array(
  'my_orderby_field' => 'mt1',
  'order' => 'ASC',
  'meta_key' => 'date1',
  'orderby' => 'meta_value',
  /* ... the rest */

function order_by_one_meta($orderby,$qry) {
  $myorder = $qry->get('my_orderby_field');
  if (!empty($myorder)) {
    global $wpdb;
    $orderby = str_replace($wpdb->postmeta,$myorder,$orderby);
  }
  return $orderby;
}
add_filter('posts_orderby','order_by_one_meta',100,2);

Adding your own field to the query is a sort of a hack. I don't know it that works by design or if it is an unintentional side effect, so it does count as a somewhat dangerous piece of code. Another way to do it is simple to create the callback:

function order_by_one_meta($orderby) {
  $orderby = str_replace($wpdb->postmeta,$myorder,$orderby);
  return $orderby;
}

Apply it and then remove it where it is needed.

add_filter('posts_orderby','order_by_one_meta',100);
$query2 = new WP_Query( $args2 ); 
remove_filter('posts_orderby','order_by_one_meta',100);

Less dangerous, not as interesting. :(

I can't test that as I don't have your data in the database, but the SQL should be right.

I am not sure why you need multiple date keys though. Using the same key multiple times-- say _scheduled_event_dates-- should give you the same effect, and would make for more efficient queries and code.