Wordpress - ACF - get post multiple times with rep

Question

I have a custom post type koncerty with custom date field datum, and I'm currently retrieving the posts with this query (which works well):

$date = date("Ymd");
$sql = <<<SQL
SELECT p.ID, p.post_content, p.post_title, datum.meta_value as datum, featured.meta_value as featured
FROM {$wpdb->posts} p
LEFT JOIN {$wpdb->postmeta} featured 
    ON featured.post_id = p.ID 
    AND featured.meta_key = 'featured'
LEFT JOIN {$wpdb->postmeta} datum 
    ON datum.post_id = p.ID 
    AND datum.meta_key = 'datum'
WHERE 
    p.post_type = 'koncert' 
    AND p.post_status = 'publish'
    AND datum.meta_value >= '$date'
  ORDER BY featured DESC, datum ASC, RAND()
LIMIT 3
SQL;

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

I would like to make the datum field repeatable, using the ACF repeater field addon. How should I modify the query, to get the post multiple times? Ie., I'll have a show with three dates, and need to display it like

• 1.1.2013 - Show 1 - info
• 2.1.2013 - Show 1 - info
• 3.1.2013 - Show 1 - info

There's some tutorial here: http://www.advancedcustomfields.com/resources/tutorials/querying-the-database-for-repeater-sub-field-values/, but with my limited sql knowledge I'm not able to put it together.

Thanks

Answer 1

I seem to got it working, here's the query:

SELECT p.ID, p.post_content, p.post_title, datum.meta_value as datum, datum.meta_key , featured.meta_value as featured
FROM wp_posts p
LEFT JOIN wp_postmeta featured 
    ON featured.post_id = p.ID 
    AND featured.meta_key = 'featured'
LEFT JOIN wp_postmeta datum 
    ON datum.post_id = p.ID 
    AND datum.meta_key LIKE  'datum2_%_datum_koncertu'
WHERE 
    p.post_type = 'koncert' 
    AND p.post_status = 'publish'
    AND datum.meta_value >= '$date'
  ORDER BY featured DESC, datum ASC, RAND()
LIMIT 3

I use 'datum2_%_datum_koncertu', because the naming convention ACF uses to save data to database is $ParentName_$RowNumber_$ChildName (see here http://www.advancedcustomfields.com/resources/tutorials/querying-the-database-for-repeater-sub-field-values/).

This way the query returns each post multiple times, and to display the date, I just use <?php date("d.m.", strtotime($post->datum))?>

Not sure, if this is possible with wp_query at all.