querying on custom meta fields and sorting them by custom meta

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.