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'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?

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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' );
share|improve this answer
2  
This makes perfect sense, the posts_orderby filter works a treat - thanks! –  paulbennett Mar 28 '12 at 13:44

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.