Reply To: Repeated Date Range Query

• regevlio

• June 11, 2015 at 12:21 am

Try this
function get_post_ids_within_range(){
global $wpdb;
$datenow = date(time());
$post__in = array();

$post_type = “properties”;
$acf_pref = “kidum_”;

$sql = ”
SELECT post_id,
substring_index(meta_key,’_’,2) as field_joiner,
max( case when substring_index(meta_key,’_’,-1) = ‘from’ AND UNIX_TIMESTAMP(meta_value) <= $datenow then meta_value end) as date_from,
max( case when substring_index(meta_key,’_’,-1) = ‘to’ AND UNIX_TIMESTAMP(meta_value) >= $datenow then meta_value end) as date_to
FROM $wpdb->postmeta
WHERE meta_key LIKE ‘$acf_pref%’
AND post_id IN(SELECT ID FROM $wpdb->posts WHERE post_type='{$post_type}’ AND post_status=’publish’)
GROUP BY post_id,field_joiner
having date_to <> ” AND date_from <> ”
” ;

$rows = $wpdb->get_results($sql);

foreach($rows as $row){
$post__in[] = $row->post_id;
}

return $post__in;
}