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

• JonasVorwerk

• July 10, 2014 at 8:12 pm

Currently I’m developing an festival / event website. I’ve got an custom post type called “programma” and a repeater field for timeslots. These timeslots have a start and stop time.

Now I would like to do a query that returns every program post that plays in a curtain hour. This is where I get lost and stuck for the last couple of days and I really could use some help here 😉

// custom filter to replace ‘=’ with ‘LIKE’
function my_posts_where( $where ){
$where = str_replace(“meta_key = ‘tijdblok_%_start_tijd'”, “meta_key LIKE ‘tijdblok_%_start_tijd'”, $where);
return $where;
}
add_filter(‘posts_where’, ‘my_posts_where’);

function my_posts_where2( $where ){
$where = str_replace(“meta_key = ‘tijdblok_%_eind_tijd'”, “meta_key LIKE ‘tijdblok_%_eind_tijd'”, $where);
return $where;
}
add_filter(‘posts_where’, ‘my_posts_where2’);

$args = array(
‘numberposts’ => -1,
‘post_type’ => ‘programma’,
‘post_status’ => ‘publish’,
‘meta_query’ => array(
array(
‘key’ => ‘tijdblok_%_start_tijd’,
‘value’ => $currenthour,
‘compare’ => ‘<=’,
‘type’ => ‘NUMERIC’,
),
array(
‘key’ => ‘tijdblok_%_eind_tijd’,
‘value’ => $currenthour,
‘compare’ => ‘>’,
‘type’ => ‘NUMERIC’,
),
)
);

The % should be a number, but the same for the start as end time… (I guess)

There are probably more people who are doing this so I dug deep into the support / forum pages and found a lot but not quite what I need.
The way the repeater field stores it’s data is not making it any easier.

Is there a solid way to do this?

Thanks in advance,

Jonas

• 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;

• jusseb

• August 25, 2014 at 11:39 pm

Hello,

I’m currently facing the same problem. I have a custom post type called ‘events’. Each event has a date, filtering on that is no problem. But each event also has multiple entries (repeater fields) each with a time. I want to be able to filter posts based on that subfield and the current time.

Did you already find a solution to your problem Jonas?

I really appreciate any help you can provide.

• JonasVorwerk

• August 25, 2014 at 11:44 pm

Hello Jusseb,
No solution yet, but a nasty workaround, a large query.
If somebody knows how to make the numbers generic we would have the solution 😉

Here is my nasty, way too long query:

$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’)
LEFT JOIN $wpdb->postmeta wpm9 ON (wposts.ID = wpm9.post_id
AND wpm9.meta_key = ‘tijdblok_4_start_tijd’)
LEFT JOIN $wpdb->postmeta wpm10 ON (wposts.ID = wpm10.post_id
AND wpm10.meta_key = ‘tijdblok_4_eind_tijd’)
LEFT JOIN $wpdb->postmeta wpm11 ON (wposts.ID = wpm11.post_id
AND wpm11.meta_key = ‘tijdblok_5_start_tijd’)
LEFT JOIN $wpdb->postmeta wpm12 ON (wposts.ID = wpm12.post_id
AND wpm12.meta_key = ‘tijdblok_6_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)
OR
(wpm9.meta_value <= $hour) AND (wpm10.meta_value > $hour)
OR
(wpm11.meta_value <= $hour) AND (wpm12.meta_value > $hour)
)
AND wposts.post_type = ‘programma’
AND wposts.post_status = ‘publish’
ORDER BY wpm1.meta_value ASC
“;