order posts by meta value on posts page

So I figured out how to get what I want, but it’s still not perfect.
The problem revolved around how my custom meta value was being stored – basically as a string.

I needed to tell MySQL that it was a date, so it could be used to order the posts. To this end, I used a custom query:

    $posts = $wpdb->get_results( 
       "SELECT wp_posts.*, 
        STR_TO_DATE(m.meta_value, '%Y-%m-%d') AS m_date 
        FROM wp_posts 
        LEFFT JOIN wp_postmeta AS m ON(wp_posts.ID=m.post_id AND m.meta_key='date')
        WHERE post_status="publish" AND post_type="post"
        ORDER BY m_date DESC"
);

if ($posts):
    global $post;
    foreach ($posts as $post):
        setup_postdata($post);
        echo get_the_title(); //finally in the right order!
    endforeach;
endif;

This uses the MySQL function STR_TO_DATE() to cast the meta value as a date, then use it to order the posts in the ORDER BY clause.

So this is all well and good but it would be much better if I could hook into the query before it’s run and alter the SQL just this little bit, I could make a big saving in terms of queries. The site is a small one so it’s not a massive concern, but every little helps.

If someone knows how to do that, I’d happily accept that answer instead of my own.

References:

http://codex.wordpress.org/Displaying_Posts_Using_a_Custom_Select_Query

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_str-to-date

Related Posts: