Custom query object with Simple-Fields custom date field

Question

I'm currently using the simple-fields plugin to add an event start date field that is a date type field to a custom post type called an 'event'. On my homepage I'm looking to display the three closest events to today's date that haven't passed today's date.

I currently have:

$recent_events = new WP_Query(array('post_type' => 'phelps_event', 'posts_per_page' => 3, 'order' => 'DESC'));

but that just gives me the first 3 events in the order they were created. I'm not sure how I would filter by the date field I created with the simple-fields plugin.

Thanks for looking!

Answer 1

As Milo says, Simple Fields currently stores the dates in a much non preferred way.

I can think of two ways to solve this right now:

One: Fetch the posts "manually" using sql and use mysql function substr to re-make the dates to a valid format that you can sort on. A bit cumbersome, but would work.

The SQL query would look something like:

SELECT 
post_id,
DATE_FORMAT(concat(substr(_your_date_meta_key, 7, 4), "-",substr(_your_date_meta_key, 4,     2),"-",substr(_your_date_meta_key, 1, 2)), '%Y %b %D') as theDate
from wp_postmeta
where meta_key = "_your_date_meta_key"
order by theDate

And you would get the post ids in return, and then you can fetch just those ids using get_post() or similar.

Two: Fetch all posts using wp_query (as you already done) and then sort them in PHP using usort(). Depending on how many posts you have this could work pretty well.

(Three: Wait a couple of days and let me update Simple Fields with support for storing the dates as unixdates, since it makes so much more sense!)

Answer 2

It seems that if you use the plugin's datepicker, it unfortunately enters the date in a format that makes it difficult to query by date. Dates should be in format yyyy-mm-dd, the plugin saves it backwards- dd-mm-yyyy which results in strange things like the 26th of September 1960 coming after the 25th of October 2012.

If it's possible to reverse the date format somehow (which is probably a question for the plugin's developer) you would simply need to get the meta key the date is saved under. In simple fields that's accomplished by clicking the Show custom field keys when editing a post, which will look something like:

Meta key: _simple_fields_fieldGroupID_1_fieldID_1_numInSet_0

For an alternative, I would recommend using something like this meta box class instead, which has a configurable date format.

That said, to query by a custom field date, you would do a meta query on the key the date is saved under:

$today = date('Y-m-d');
$args = array(
    'posts_per_page' => 3,
    'meta_key' => '_your_date_meta_key',
    'meta_value' => $today,
    'meta_compare' => '>=',
    'orderby' => 'meta_value',
    'order' => 'ASC'
);
query_posts( $args );