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.

Currently I have the loop listing posts between two dates using the posts_where filter:

 function filter_where( $where = '' ) {
  $where .= " AND post_date >= '2000-01-01' AND post_date <= 2004-12-31' ";
  return where;
 }
 add_filter('posts_where', 'filter_where');
 query_posts($query_string);
 while (have_posts()) :
   the_post();
   the_content();
 endwhile;

In a meta_key called original_date I have stored a different date for each post that I wish to use instead of the post_date. How do I correctly call up this meta_key into the $where query to fulfil the same purpose as the post_date?

$where .= " AND [meta_key=original_date] >= '2000-01-01' AND [meta_key=original_date] <= '2004-12-31' ";

For example, the following SQL query doesn't seem to work, even though correct values (2001-10-29, 2004-11-03, etc.) are present in the original_date meta_key of the posts:

global $wpdb;
 $where .= " AND (SELECT meta_value FROM $wpdb->postmeta WHERE meta_key='original_date' ) >= '2000-01-01' AND (SELECT meta_value FROM $wpdb->postmeta WHERE meta_key='original_date' ) <= '2004-12-31' ";

whereas the following works fine on the same posts using their post_date as reference:

$where .= " AND post_date >= '2000-01-01' AND post_date <= 2004-12-31' ";

Perhaps the meta_value array needs to be stripped of some extraneous material in order to format the results into the same form as post_date? How can we approach this?

share|improve this question
    
try $where .= " AND (($wpdb->postmeta.meta_key = 'original_date' AND $wpdb->postmeta.meta_value >= '2000-01-01') AND ($wpdb->postmeta.meta_key = 'original_date' AND $wpdb->postmeta.meta_value <= '2004-12-31')) "; –  onetrickpony Dec 17 '10 at 20:43
    
Worked perfectly. Thanks so much. –  maduroblanco Dec 17 '10 at 21:03
add comment

2 Answers

up vote 3 down vote accepted

Thanks to AmbitiousAmoeba for the answer. The following improved code solves the problem:

function filter_where( $where = '' ) {
   global $wpdb;

   $where .= " AND (($wpdb->postmeta.meta_key = 'original_date' AND $wpdb->postmeta.meta_value >= '2000-01-01') AND ($wpdb->postmeta.meta_key = 'original_date' AND $wpdb->postmeta.meta_value <= '2004-12-31')) ";
   return where;
}
add_filter('posts_where', 'filter_where');
query_posts($query_string);
while (have_posts()) :
   the_post();
   the_content();
endwhile;
share|improve this answer
    
I get the following error when I run the final query in phpMyAdmin #1054 - Unknown column 'wp_postmeta.meta_key' in 'where clause'. Can you help with that? –  afnrf Jan 10 '12 at 11:32
add comment

If you have Unknown column 'wp_postmeta.meta_key' in 'where clause

you probably want to join $wpdb->postmeta table into this query:

function custom_posts_join($join){
     global $wpdb;

     $join .= " LEFT JOIN $wpdb->postmeta ON $wpdb->posts.ID = $wpdb->postmeta.post_id ";

     return $join;
}
add_filter( 'posts_join' , 'custom_posts_join');
share|improve this answer
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.