Modify query after meta value

Question

I would like to change the calendar so it will display some posts based on a custom date field. For now i am interested to modify this query:

$wpdb->get_results("SELECT ID, post_title, DAYOFMONTH(post_date) as dom "
    ."FROM $wpdb->posts "
    ."WHERE post_date >= '{$thisyear}-{$thismonth}-01 00:00:00' "
    ."AND post_date <= '{$thisyear}-{$thismonth}-{$last_day} 23:59:59' "
    ."AND post_type = 'post' AND post_status = 'publish'"
);

to something like this:

$wpdb->get_results("SELECT a.ID, a.post_title, DAYOFMONTH(b.meta_value) as dom "
    ."FROM $wpdb->posts a, $wpdb->postmeta b "
    ."WHERE b.meta_value >= '{$thisyear}-{$thismonth}-01 00:00:00' "
    ."AND b.meta_value <= '{$thisyear}-{$thismonth}-{$last_day} 23:59:59' "
    ."AND a.post_type = 'post' AND a.post_status = 'publish' AND a.ID = b.postid AND b.meta_key='Event Date'"
);

I did try the above query but it does not work, it returns an empty array. How can I change that the calendar will look after the meta value instead of post_date ? Thank you.

Answer 1

Here's an example using WP_Query's meta_query to query posts between two meta key values:

$meta_query = array(
    array(
        'key' => 'event_date',
        'value' => array( $date_start, $date_end ),
        'compare' => 'BETWEEN'
    )
);

$args = array(
    'posts_per_page' => -1,
    'meta_key' => 'event_date',
    'orderby' => 'meta_value',
    'meta_query' => $meta_query
);

$results = new WP_Query( $args );