I have two custom meta values that I am using for an events list and have almost got everything working perfectly except for the ordering. I have two dates (event_date and event_enddate). One of these is a start date which is always present, and the other is an end date which is only used when needed. I have created a query which lists all dates where either of these fields are equal or greater to today's date, which works fine. However, I want all of the events to be ordered solely by the event_date field. How can I do this? I've spent all day trying to do this and just can't work it out!
Here's my code:
SELECT DISTINCT wposts.* FROM $wpdb->posts wposts LEFT JOIN $wpdb->postmeta wpostmeta ON wposts.ID = wpostmeta.post_id LEFT JOIN $wpdb->term_relationships ON (wposts.ID = $wpdb->term_relationships.object_id) LEFT JOIN $wpdb->term_taxonomy ON ($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id) WHERE ((wpostmeta.meta_key = 'event_enddate' AND wpostmeta.meta_value >= CURDATE()) OR (wpostmeta.meta_key = 'event_date' AND wpostmeta.meta_value >= CURDATE())) AND wposts.post_status = 'publish' AND $wpdb->term_taxonomy.taxonomy = 'category' AND $wpdb->term_taxonomy.term_id IN(15) AND wposts.post_status = 'publish' ORDER BY wpostmeta.meta_value ASC