Foreach loop inside loop : is it possible to sort the results?

Question

On a WP website, I have a custom post type ("Clients") with 3 posts : John, Jack and Mary. Inside each post, I have a "payments" repeater with 2 subfields ("date" and "amount").

I have a query that loops through all the posts(clients) and puts the payments in a table - 1 row per payment.

John - 12/01/2022 - 50€
John - 23/02/2022 - 170€
Jack - 02/12/2021 - 1000€
Jack - 07/03/2022 - 4000€
Mary - 10/02/2022 - 100€

Is there any way to order the rows of this table by date of payment ? So as I get to this result:

Jack – 02/12/2021 – 1000€
John – 12/01/2022 – 50€
Mary – 10/02/2022 – 100€
John – 23/02/2022 – 170€
Jack – 07/03/2022 – 4000€

I understand that I might have to do a complete different query, but I'm wondering if such a query is even possible, or if I have to put my payments in a separate custom post type (1 post per payment) in order to be able to sort them.

Here is my code:

<table>

<?php    $args = array(
        'post_type' => 'clients',
        'posts_per_page' => '-1',
    );

    $post_query = new WP_Query($args);

    if($post_query->have_posts() ) {
        while($post_query->have_posts() ) {
            $post_query->the_post();
        
    $payments = get_field('payments');  
        
      if( $payments ) { 
        foreach( $payments as $payment ) {      
        
        $date = $payment['date'];
        $amount = $payment['amount'];
            ?>
                
  <tr>
    <td><?php echo the_title(); ?></td>
    <td><?php echo $date; ?></td>
    <td><?php echo $amount; ?></td>
  </tr>
            
            <?php   }}}}  ?>
</table>

Answer 1

Changing the query is probably not the best strategy here. You can instead use a temporary array to store the data, then sort the array.

1. At the top of your script, define the array

$data = [];

2. Remove the HTML from the foreach, and instead populate the array

foreach ($payments as $payment) {
    $date = $payment['date'];
    $amount = $payment['amount'];
    $data[] = [
        'title' => get_the_title(),
        'date' => $date,
        'amount' => $amount
    ];
}

3. Once the loop is ended, sort the array with a custom usort function based on the date

usort($data, function($a, $b) {return strtotime($a['date']) - strtotime($b['date']);})

4. Finally use the array to loop and echo your table

<table>
    <?php foreach($data as $d) { ?>
    <tr>
        <td><?php echo $d['title']; ?></td>
        <td><?php echo $d['date']; ?></td>
        <td><?php echo $d['amount']; ?></td>
    </tr>
    <?php } ?>
</table>

Complete Code

<?php
$data = [];
$args = array(
    'post_type' => 'clients',
    'posts_per_page' => '-1',
);
$post_query = new WP_Query($args);
if ($post_query->have_posts()) {
    while ($post_query->have_posts()) {
        $post_query->the_post();
        $payments = get_field('payments');
        if ($payments) {
            foreach ($payments as $payment) {
                $date = $payment['date'];
                $amount = $payment['amount'];
                $data[] = [
                    'title' => get_the_title(),
                    'date' => $date,
                    'amount' => $amount
                ];
            }
        }
    }
}
usort($data, function($a, $b) {return strtotime($a['date']) - strtotime($b['date']);})

?>
<table>
    <?php foreach($data as $d) { ?>
    <tr>
        <td><?php echo $d['title']; ?></td>
        <td><?php echo $d['date']; ?></td>
        <td><?php echo $d['amount']; ?></td>
    </tr>
    <?php } ?>
</table>