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 build a query in order to sort some posts by a date custom field, where the dates are formatted dd/mm/yyyy.

It works by querying the database directly with get_results like in this example :

$querystr = "
    SELECT *
    FROM wp_posts, wp_postmeta
    WHERE wp_posts.ID = wp_postmeta.post_id
    AND wp_postmeta.meta_key = 'my_cutom_date'
    AND wp_posts.post_status = 'publish'
    AND wp_posts.post_type = 'post'
    ORDER BY STR_TO_DATE(wp_postmeta.meta_value, '%d/%m/%Y') ASC
";

But, I would like to transpose this behavior to a WP_Query, using a posts_where filter. I got as far as this :

$args = array(
    'post_type' => 'post',
    'post_status' => 'publish',
    'posts_per_page' => 10,
    'meta_key' => 'my_custom_date'
); 

function filter_orderby( $orderby = '' ) {  
    global $wpdb;
    $orderby .= " ORDER BY STR_TO_DATE($wpdb->postmeta.meta_value, '%d/%m/%Y') ASC ";
    return $orderby;
}

add_filter( 'posts_orderby', 'filter_orderby' );

But it doesn't seem to return anything. what am I missing ?

share|improve this question
1  
Do you have the resulting SQL query that gets executed? Filter on posts_request and var_dump() what gets passed, or install the Debug Bar with SAVEQUERIES enabled. I think the problem is that you don't have to specify ORDER BY yourself. –  Jan Fabry Jun 1 '11 at 8:42
    
@Jan, The Debug bar is great, thanks a lot for that. And yes there is an error in the SQL. The thing is there are 2 ORDER statements, one default (I guess) and the one I add in my filter : ORDER BY cs_posts.post_date DESC ORDER BY STR_TO_DATE(cs_postmeta.meta_value, '%d/%m/%Y') ASC How can I remove the ORDER BY cs_posts.post_date DESC ? –  mike23 Jun 1 '11 at 11:14
    
@Jan, if I remove ORDER BY the result is the same, I get an error. I also tried with AND, for ex :ORDER BY cs_posts.post_date DESC AND STR_TO_DATE(cs_posts.meta_value, '%d/%m/%Y'), but with no chance. Now I'm wondering, the filter lets you only add things to the query ? It's impossible to overwrite the defaults ? –  mike23 Jun 1 '11 at 13:08
    
@myself, of course it is, by using "=" instead of ".=", as usual in php, duh. –  mike23 Jun 1 '11 at 13:20
add comment

1 Answer

up vote 2 down vote accepted

The filter on posts_orderby should return a string that does not begin with ORDER BY, WordPress will add that itself.

By default WordPress will filter by post_date. If you don't want that, you should overwrite the order clause, not append to it. So your filter should look like this:

function filter_orderby( $orderby ) {  
    global $wpdb;
    return " STR_TO_DATE({$wpdb->postmeta}.meta_value, '%d/%m/%Y') ASC ";
}
share|improve this answer
    
Thanks Jan, it works ! –  mike23 Jun 1 '11 at 13:20
add comment

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.