Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

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

share|improve this question
1  
Is there a reason you're not just using a WP_Query tax and meta query? –  Milo Sep 2 '12 at 3:41
1  
uhm... I don't know if it's possible to do what I want using Wordpress function, basically I need to get something as "give me the next three events from today's date (compare meta field) which happen to be in this location (term)"). I don't know how compare a custom field value with present time while querying the posts using a wp function. If this can be accomplished elegantly using WP_Query it should be ok. The code above is supposed to run in a widget. EDIT - oh yeah, looks possible, I'll give it a try –  Fulvio Sep 2 '12 at 3:49
    
If you work this out on your own, please add it as an answer and mark it as accepted. :) –  Brian Fegter Sep 2 '12 at 4:11
    
I edited my original post, unfortunately it doesn't work yet –  Fulvio Sep 2 '12 at 4:17
    
@Fulvio - I think you're close, see my answer –  Milo Sep 2 '12 at 4:28
add comment

1 Answer

up vote 1 down vote accepted

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'
);
share|improve this answer
    
ops! 5 out of 10 turns always to be the typo... the kind debug mode cannot catch... anyway there was also something else, I forgot to put 'order' => ASC, otherwise won't work right for my case (upcoming events), thank you very much for helping me sorting it out –  Fulvio Sep 2 '12 at 4:37
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.