Support

Account

Home Forums Add-ons Repeater Field Repeater subfield meta query (event, multiple start stop times)

Solving

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

  • 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

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

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

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

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic.