Sort by Repeater Date Field

• jarvis

• September 9, 2013 at 9:18 pm

Hi All,

I really hope someone can help! I’ve created a custom post type called Courses. Each course can have multiple dates, so I’ve therefore opted for the ACF repeater field and used the Date Picker.

However, I’m struggling to list the courses in Date order (ideally monthly). My code so far lists the courses and their dates, however, I can’t work out how to then put the course list into months and show the course under the relevant month.

Here’s my code so far:

<?php	
$paged = (get_query_var('paged')) ? get_query_var('paged') : 1;
$loop = new WP_Query( array (
	'post_type' => 'courses',
	'posts_per_page' => 100,
	'order'    => 'DESC',				
	'meta_key' => 'dates_0_available_dates',						
	'paged'	=>	$paged
) );
echo '<ul>';
while ( $loop->have_posts() ) : $loop->the_post();
?>
													
<?php 
$rows = get_field('dates');
if($rows)
{
	echo '<ul>';
 
	foreach($rows as $row)
	{
		#echo '<li>sub_field_1 = ' . $row['available_dates'] . '<br>';
		echo '<li>';
		$myDate = $row['available_dates']; 
		$tempDate = date_create("$myDate");
		$newDate = date_format($tempDate, 'ymd');
		echo 'Date  '.$newDate.'<br>';	
		
		$month = date("m",strtotime($myDate));
		echo 'Month= '.$month.'<br>';
		
		$year = date("y",strtotime($myDate));
		echo 'Year= '.$year.'</li>';		

		#now we need to do another loop, but foreach month, where course contains date $month
		
		$posts = get_posts(array(
			'numberposts' => -1,
			'post_type' => 'courses',
			'meta_key' => 'available_dates',
			'meta_value' => $month
		));
		 
		if($posts) {
			the_title();
		}
		
	
	}
 
	echo '</ul>';
}
?>

The result is:
Course 1
Date 130912
Month= 09
Year= 13
Date 131031
Month= 10
Year= 13
Date 140418
Month= 04
Year= 14

Course 2
Date 130926
Month= 09
Year= 13

Course 3
Date 131030
Month= 10
Year= 13

What I need to do is then
September
– Course 1
– Course 2

October
– Course 1
– Course 3

etc. Any help is very much appreciated!

• Elliot

• September 10, 2013 at 3:51 pm

Hi @jarvis

In SQL, you can only sort results based on 1 value in the row. So the concept of sorting a post based on multiple date values doesn’t work.

Are you actually trying to do this:
Sort the posts based on the earliest date found in the repeater?

Clarification would be great

Thanks
E

• jarvis

• September 10, 2013 at 5:56 pm

Thanks @elliot for the reply.

I’ve started to look at the SQL method as I think this may work, however, I’ve come unstuck with the code. I know if I alter the SQL a little I can get it more or less to work for one month.

So I’ve altered the code to loop for the next 6 months but it only returns 1 result

Here’s my code:

for ($x=1; $x<=6; $x++) {
	#echo "Month is: $x <br>";

	$date = new DateTime("$x months");
	$date->modify("-" . ($date->format('j')-1) . " days");
	#echo $date->format('j, m Y');
	
	$month =  $date->format('m');
	$year =  $date->format('Y');
	
	echo 'Month= '.$month .' Year= '.$year.' <br>'; #debug

	$rows = $wpdb->get_results($wpdb->prepare( 
	"
	SELECT * 
	FROM upKeep_postmeta
	WHERE meta_key LIKE %s
		AND meta_value LIKE %s
	",
	'dates_%_available_dates', // meta_name: $ParentName_$RowNumber_$ChildName
	''.$year.''.$month.'%' // meta_value: 20131031 for example
	));
			
	// loop through the results
	if( $rows ) {
		echo '<ul>';
		foreach( $rows as $row ) {
			// for each result, find the 'repeater row number' and use it to load the sub field!
			preg_match('_([0-9]+)_', $row->meta_key, $matches);
			$meta_key = 'dates_' . $matches[0] . '_dates'; // $matches[0] contains the row number!
			?>
			<li><a href="<?php get_permalink( $row->post_id ); ?>"><?php echo get_the_title( $row->post_id ); ?></a></li>
			<?php
		}
		echo '</ul>';
	}

}

I think I’m being daft as I believe the above “should” work. Any help is much appreciated!

• jarvis

• September 10, 2013 at 6:41 pm

Ok, this now works as I need it. I wonder if the code can be tidied in anyway but it works. Here’s my code incase it helps anyone else.

<?php
#start from current month. Change 6 to however months ahead you want
for ($x=0; $x<=6; $x++) {

	$date = new DateTime("$x months");
	$date->modify("-" . ($date->format('j')-1) . " days");
	#echo $date->format('j, m Y');	
	$month =  $date->format('m');
	$year =  $date->format('Y');	
	#echo 'Month= '.$month .' Year= '.$year.' <br>'; #debug

	$rows = $wpdb->get_results($wpdb->prepare( 
	"
	SELECT * 
	FROM upKeep_postmeta
	WHERE meta_key LIKE %s
		AND meta_value LIKE %s
	",
	'dates_%_available_dates', // meta_name: $ParentName_$RowNumber_$ChildName
	#''.$year.''.$month.'%' // meta_value: 20131031 for example
	''.$year.''.$month.'%' // meta_value: 20131031 for example
	));
			
	// loop through the results
	if( $rows ) {
		echo '<h2>'.$date->format('F').' '.$date->format('Y').'</h2>';
		echo '<ul>';
		foreach( $rows as $row ) {
			// for each result, find the 'repeater row number' and use it to load the sub field!
			preg_match('_([0-9]+)_', $row->meta_key, $matches);
			$meta_key = 'dates_' . $matches[0] . '_dates'; // $matches[0] contains the row number!
			?>
			<li><a href="<?php get_permalink( $row->post_id ); ?>"><?php echo get_the_title( $row->post_id ); ?></a></li>
			<?php
		}
		echo '</ul>';
	}
}
?>

• Skrabbel

• June 13, 2016 at 10:14 am

I was looking for a solution for the same problem, but this isn’t a solution. Because the data exists in a database and sort them with php in this way is for a few more dates inperformant and not useable with paginations and so on.