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

share|improve this question
    
Have you tried storing the date as a unix timestamp? –  m0r7if3r May 24 '12 at 13:33
    
nope, will give it a try if you think it will help –  jammypeach May 24 '12 at 13:35
    
The date with hyphens must be the culprit. Try meta_value instead of meta_value_num cuz with those hyphens, it's a string. –  Rutwick Gangurde May 24 '12 at 13:42
    
@RutwickGangurde just tried, sorry but same as before. I think you're right though, it must be to do with this custom date being stored as text rather than a date. Just playing with the DB at the moment... –  jammypeach May 24 '12 at 13:44
    
Yep, I bet storing the date right will solve your problem... –  Rutwick Gangurde May 24 '12 at 14:02
show 1 more comment

1 Answer

up vote 0 down vote accepted

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

share|improve this answer
    
Is there any particular reason you're avoiding the unix date format? –  m0r7if3r May 24 '12 at 14:43
    
@m0r7if3r yes, the field is filled out with a js datepicker and having a unix timestamp might be confusing to the user even if they don't need to set it by hand. however, I've tried it and it's another solution to this problem: sqlfiddle.com/#!2/d77da/3 –  jammypeach May 24 '12 at 16:19
    
str_to_time() is your friend. –  m0r7if3r May 24 '12 at 19:02
    
@m0r7if3r that is certainly another option, however it gives birth to another question - how to change the meta value (i.e running str_to_time() on it) before it's saved on wp-admin. And the effect is still the same - I'm going to require another query regardless, the only difference is that I can use query_posts() instead of writing the SQL myself. If you have a better way, feel free to post an answer and if it's good I'll gladly accept it over my own –  jammypeach May 25 '12 at 9:49
    
You should be able to run it during your data validation and have the same number of queries as if you didn't convert it. –  m0r7if3r May 25 '12 at 12:49
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.