I'm writing a custom WordPress query to use some of the data which the Woothemes Diarise theme creates. Diarise is an event planner theme with calendar blah, blah... and uses custom fields to store the event start and end dates in WP custom fields in the *wp_postmeta* table, which implements a key-value store. So for each post in the "event" category, there are 2 records in *wp_postmeta*, named *event_start_date* and *event_end_date* that I'm interested in.
The task is to compare a tourist's arrival and departure dates with the start and end dates of events, yielding a what's on list of events available. We thought we'd killed it with a grand flash of logic, that goes like this:
Disregard any event that ends before the tourist arrives, and any that begin after the departure date.
I wrote this query:
SELECT wposts.*
FROM wp_posts wposts
LEFT JOIN wp_postmeta wpostmeta ON wposts.ID = wpostmeta.post_id
LEFT JOIN wp_term_relationships ON (wposts.ID = wp_term_relationships.object_id)
LEFT JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)
WHERE wp_term_taxonomy.taxonomy = 'category' AND wp_term_taxonomy.term_id IN(3,4)
AND ( wpostmeta.meta_key = 'event_start_date'
AND NOT ( concat(subst(wpostmeta.meta_value,7,4),'-',subst(wpostmeta.meta_value,4,2),'-',subst(wpostmeta.meta_value,1,2) > '2010-07-31' ) )
AND ( wpostmeta.meta_key = 'event_end_date'
AND NOT ( concat(subst(wpostmeta.meta_value,7,4),'-',subst(wpostmeta.meta_value,4,2),'-',subst(wpostmeta.meta_value,1,2) < '2010-05-01' ) ) )
ORDER BY wpostmeta.meta_value ASC
And, of course it returns no records. The problem I believe is in the dual reference to wpostmeta.meta_key, but how to get around that?