How to correctly call custom field dates into a posts_where filter using SQL statements

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?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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;

Method 2

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');

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0