order posts by meta value on posts page

Question

I've been reading alot about how to order posts by a meta_value, but I can't seem to get it to work for me.

My meta_key is called date, and it is stored as yyyy-mm-dd.

Here's where I've gotten to so far:

$args = array(
    'posts_per_page' => -1,
    'meta_key' => 'date',
    'orderby' => 'meta_value_num',
    'order' => 'DESC',
    'post_type' => 'post',
    'post_status' => 'publish'         
);
query_posts($args);
if (have_posts()) while (have_posts()) : the_post(); 

As far as I understand, this should give me what I want - but doing this on the index.php page doesn't seem to change the query. The posts seem to still be ordered by post_date rather than my date meta value, which is different.

What I want to do is override or change the query on the posts and archive pages so that it will order by my meta_key. It is present on every post so there is no issue with what to do if it's not there.

Have I got the arguments right, and if so, what more do I need to do to get this working?

Any help appreciated

Update:

I tried using WP_Query() with these arguments and printed out the object. The request method of the object ordered by menu_order ASC and completely ignored my meta_value_num argument. So this is why the ordering doesn't work, but the rest of the query was fine. Any ideas why WP is ignoring my order args?

Another Update

OK, narrowing it down. The date is stored as text in the postmeta table, so trying to order by it and expecting MySQL to treat it like a date is a bit silly. To that end, I wrote my own query which gets what I need:

SELECT wp_posts.post_title,
       m.*, 
       STR_TO_DATE(m.meta_value, '%Y-%m-%d') AS m_date
FROM wp_posts  
LEFT JOIN wp_postmeta 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

Notice the STR_TO_DATE() call? This casts the string as a date, allowing MySQL to order by it correctly. Phew. Now to edit my loop....

Alternatively, does anyone know a hook I could use to edit the query in place as it's called? It would be alot more efficient than running two queries.

Answer 1

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