Hello I need to do a query that counts the posts that have meta_key like status and meta_value like Unplaced, and the same post has a meta_key like issue_date and meta_value like a date between two days. For example, I have a table like this:
id_meta post_id meta_key meta_valuue
1 1 status Unplaced
2 1 issue_date 20160428
3 2 status VIP
4 2 issue_date 20160428
5 3 status Placed
6 3 issue_date 20160427
7 4 status Unplaced
8 4 issue_date 20160420
I want that the query only returns 1 because only one post has status Unplaced and the issue_date between 21 and 28. The query that I wrote is:
$results = $wpdb->get_var("
SELECT COUNT(*)
FROM wp_posts
, wp_postmeta
WHERE meta.key = 'status'
AND meta_value LIKE '%Unplaced%'
AND post_id = ID
AND post_status <> 'trash'
AND post_id IN ( SELECT post_id
FROM wp_postmeta
WHERE meta_key = 'issue_date'
AND meta_value >= '20160421'
AND meta_value <= '20160428')
");
I need that the posts don't be in trash.
Thank you!!!!
E.g.:
SELECT s.post_id
, s.meta_value status
, i.meta_value issue_date
FROM wp_posts s
JOIN wp_posts i
ON i.post_id = s.post_id
WHERE s.meta_key = 'status'
AND s.meta_value = 'Unplaced'
AND i.meta_key = 'issue_date'
AND i.meta_value BETWEEN '20160421' AND '20160428';
You can construct a meta_query args and pass to WP_Query like this
$args = array(
'meta_query' => array(
array(
'key' => 'status',
'value' => 'Unplaced',
'compare' => '=',
),
array(
'key' => 'issue_date',
'value' => array( '2016-04-21', '2016-04-28' ),
'type' => 'date',
'compare' => 'BETWEEN',
),
),
);
$query = new WP_Query( $args );
//Then get count like this
$query->found_posts;
The type DATE should be in the format YYYY-MM-DD for compare value BETWEEN to work.