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

2 Views

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?

Read More
$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?

Post Views: 2

Related posts

Leave a Reply

2 comments

  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;

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