The question:
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?
The Solutions:
Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.
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