$wpdb query a post type within a specific taxonomy term while ordering posts by custom meta value?

Question

I have a custom post type 'event', a custom meta field 'event_date' and a custom taxonomy 'locations'.

I want to query $wpdb to retrieve posts in this way

1. posts must be of the 'event' post type
2. 'events' must be associated with a certain $location term within the 'locations' taxonomy
3. results have to be ordered by the 'event_date' custom meta value (which is actually a date in yymmdd format), compared with today's present date

I'm trying with the following query parameters (I can get the $location value - either ID or slug - correctly and pass to this query):

SELECT  $wpdb->posts.* 
LEFT JOIN $wpdb->postmeta ON($wpdb->posts.ID = $wpdb->postmeta.post_id)
LEFT JOIN $wpdb->term_relationships ON($wpdb->posts.ID = $wpdb->term_relationships.object_id)
LEFT JOIN $wpdb->term_taxonomy ON($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
LEFT JOIN $wpdb->terms ON($wpdb->term_taxonomy.term_id = $wpdb->terms.term_id)
FROM    $wpdb->posts, $wpdb->postmeta
WHERE   $wpdb->posts.ID = $wpdb->postmeta.post_id 
AND     $wpdb->terms.term_id = $location
AND     $wpdb->term_taxonomy.taxonomy = 'locations'
AND     $wpdb->posts.ID = $wpdb->postmeta.post_id 
AND     $wpdb->posts.post_type = 'event'
AND     $wpdb->posts.post_status = 'publish' 
AND     $wpdb->postmeta.meta_key = 'event_date'
AND     $wpdb->postmeta.meta_value > NOW()
ORDER   BY $wpdb->postmeta.meta_value ASC
LIMIT   $numberofposts

the query is not working; if I remove the JOIN part and the taxonomy part, it will work, ordering all the results by comparing 'event_date' meta with NOW() date.

I guess I'm doing it wrong with the taxonomy part...

As advised, I tried to do a WP_Query rather than a $wpdb query:

$args = array(
    'post_type' => 'event',
    'tax_query' => array(
        array(
            'taxonomy' => 'locations',
            'field' => 'id',
            'terms' => $location // location term id
        )
    ),
    'meta_key' => 'event_date',  // this meta field stores event date in yymmdd format
    'meta_value' => $today,  // this would be today's date in yymmdd format
    'meta_compare' => '>=',
    'posts_per_page' => $numberofposts, // this variable stores the number of posts I want to get
    'orderby' => 'meta_value_num'
);

However, in this latter case, the query will return ALL the posts under the specificed post_type, regardless of any other specification within $args including the sorting order

I've tried using meta_query instead of meta_key but result doesn't change

Answer 1

You have a typo in orderby, and meta_value_num is only used as an orderby value, try this:

$args = array(
    'post_type' => 'event',
    'tax_query' => array(
        array(
            'taxonomy' => 'locations',
            'field' => 'id',
            'terms' => $location // location term id
        )
    ),
    'meta_key' => 'event_date',  // this meta field stores event date in yymmdd format
    'meta_value' => $today,  // this would be today's date in yymmdd format
    'meta_compare' => '>=',
    'posts_per_page' => $numberofposts, // this variable stores the number of posts I want to get
    'orderby'=> 'meta_value_num'
);