Welcome to WebmasterWorld Guest from 54.144.77.26

Forum Moderators: rogerd & travelin cat

Message Too Old, No Replies

meta value has a date, how to use order by?

     

indyank

12:04 pm on Jul 5, 2013 (gmt 0)

WebmasterWorld Senior Member 5+ Year Member



I have a custom field called event_date (meta_key). Now value for this custom field are stored in "dd month year" format. Eg : 01 July 2013.

How do I sort the wordpress query by this custom field?

I have tried this in pre_get_posts filter fucntion, but it doesn't seem to work..

$query->set('meta_key' , 'event_date');
$query->set('orderby' , 'STR_TO_DATE( meta_value, "%d %M %Y" )');

I have found a few answers on the web where storing dates in yyyy/mm/dd format has worked with this.

$query->set('meta_key' , 'event_date');
$query->set('orderby' , 'meta_value');

But in my case, the date is stored in a different format. meta_value is a column of longtext data type.

Can someone pls. help.

lorax

12:44 pm on Jul 5, 2013 (gmt 0)

WebmasterWorld Senior Member lorax is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month



What version of MySQL and PHP are you running?

indyank

1:34 pm on Jul 5, 2013 (gmt 0)

WebmasterWorld Senior Member 5+ Year Member



PHP - 5.4.9
Mysql - 5.5.31

But I think it is a wordpress limitation as I am trying to alter the main query in wordpress.

lorax

3:42 pm on Jul 5, 2013 (gmt 0)

WebmasterWorld Senior Member lorax is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month



Off the top of my head I agree with you. Have you tried creating your own query to see if you can manipulate the result set?

indyank

5:38 pm on Jul 5, 2013 (gmt 0)

WebmasterWorld Senior Member 5+ Year Member



Unfortunately, crating my own query wasn't an option. So the workaround was to somehow convert the existing meta-values for 'event_date' meta_keys into the ISO format "yyyy-mm-dd" and then this worked.

$query->set('meta_key' , 'event_date');
$query->set('orderby' , 'meta_value');

Thanks lorax.