WP_Query filter and order by meta ordering by wrong joined table

Question

I'm trying to both filter and order a Wordpress query using the meta_value, however the ORDER BY part of the query seems to be working on the wrong table.

The query params here are:

array(12) {
  ["post_type"]=>"op-events"
  ["paged"]=>1
  ["meta_query"]=>
  array(2) {
    ["relation"]=>"OR"
    [0]=>
    array(4) {
      ["key"]=>"_simple_fields_fieldGroupID_9_fieldID_1_numInSet_0"
      ["value"]=>"2012-03-28"
      ["compare"]=>">"
      ["type"]=>"DATE"
    }
  }
  ["meta_key"]=>"_simple_fields_fieldGroupID_9_fieldID_1_numInSet_0"
  ["orderby"]=>"meta_value"
  ["order"]=>"ASC"
}

The idea is to filter by dates after today, then order the posts in ascending order by date. I might normally resort to building this query manually but you'll see the "_simple_fields" meta_key, this is because the fields are created by the Simple Fields plugin and thus I don't know what their keys will be.

The resulting query generated is:

SELECT SQL_CALC_FOUND_ROWS wp_posts.* 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) 
WHERE 1=1 
AND wp_posts.post_type = 'op-events' 
AND
(
    wp_posts.post_status = 'publish' 
    OR wp_posts.post_status = 'private'
) 
AND
(
    wp_postmeta.meta_key = '_simple_fields_fieldGroupID_9_fieldID_1_numInSet_0' 
    OR
    (
        mt1.meta_key = '_simple_fields_fieldGroupID_9_fieldID_1_numInSet_0' 
        AND CAST(mt1.meta_value AS DATE) > '2012-03-28'
    )
) 
GROUP BY wp_posts.ID 
ORDER BY wp_postmeta.meta_value ASC 
LIMIT 0, 10

For some reason this query isn't returning rows in the right order, the only thing I can think of is that the ORDER BY wp_postmeta.meta_value ASC line should actually be ordering by my1.meta_value.

Can anyone shed any light on this?

Answer 1

^{Note: Only read briefly, so I might missunderstood your Q.}

JOIN on meta_query/post_meta table is pretty ... hm, funny(?) ... as you'll JOIN the table (again) for every key you add - this is a known problem (read on trac). In detail INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) is the same as INNER JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id). And ORDER BY wp_postmeta.meta_value ASC is the same as ORDER BY mt1.meta_value ASC.

You could try using the posts_clauses or posts_orderby filter to change the ORDER BY statement:

function wpse_change_orderby( $orderby )
{
    return 'ORDER BY DATE ASC';
}
add_filter( 'posts_orderby', 'wpse_change_orderby' );