Reply To: Repeater subfield meta query (event, multiple start stop times)

• JonasVorwerk

• July 28, 2014 at 4:41 pm

Got a “dirty” solution for just 4 timeslots. Is there somebody who knows how to make this query generic?

Lots of thank you’s in advance!!!

global $wpdb;
global $post;
$querystr = ”
SELECT DISTINCT wposts.*
FROM $wpdb->posts wposts
LEFT JOIN $wpdb->postmeta wpm1 ON (wposts.ID = wpm1.post_id
AND wpm1.meta_key = ‘tijdblok_0_start_tijd’)
LEFT JOIN $wpdb->postmeta wpm2 ON (wposts.ID = wpm2.post_id
AND wpm2.meta_key = ‘tijdblok_0_eind_tijd’)
LEFT JOIN $wpdb->postmeta wpm3 ON (wposts.ID = wpm3.post_id
AND wpm3.meta_key = ‘tijdblok_1_start_tijd’)
LEFT JOIN $wpdb->postmeta wpm4 ON (wposts.ID = wpm4.post_id
AND wpm4.meta_key = ‘tijdblok_1_eind_tijd’)
LEFT JOIN $wpdb->postmeta wpm5 ON (wposts.ID = wpm5.post_id
AND wpm5.meta_key = ‘tijdblok_2_start_tijd’)
LEFT JOIN $wpdb->postmeta wpm6 ON (wposts.ID = wpm6.post_id
AND wpm6.meta_key = ‘tijdblok_2_eind_tijd’)
LEFT JOIN $wpdb->postmeta wpm7 ON (wposts.ID = wpm7.post_id
AND wpm7.meta_key = ‘tijdblok_3_start_tijd’)
LEFT JOIN $wpdb->postmeta wpm8 ON (wposts.ID = wpm8.post_id
AND wpm8.meta_key = ‘tijdblok_3_eind_tijd’)
WHERE
(
(wpm1.meta_value <= $hour) AND (wpm2.meta_value > $hour)
OR
(wpm3.meta_value <= $hour) AND (wpm4.meta_value > $hour)
OR
(wpm5.meta_value <= $hour) AND (wpm6.meta_value > $hour)
OR
(wpm7.meta_value <= $hour) AND (wpm8.meta_value > $hour)
)
AND wposts.post_type = ‘programma’
AND wposts.post_status = ‘publish’
ORDER BY wpm1.meta_value ASC
“;

$wpdb->show_errors();
$result = $wpdb->get_results($querystr);

foreach ($result as $post):
setup_postdata($post);
get_template_part(‘loop-programma’);
endforeach;