Query 'orderby' when there are multiple values for the same meta_key

Question

I'm trying to create a custom sortable admin column for a custom post type that contains a "repeater" field. The plugin I'm using for this field saves the values as separate entries in the database under the same meta_key but with different meta_id. For example, what's saved in the database could be:

meta_id  post_id  meta_key    meta_value
4516     109      dates_date  1732752000
4518     109      dates_date  1732838400
4520     109      dates_date  1732924800

The repeater field holds dates (timestamps). I have defined two custom admin columns: "start date" and "end date". I get all the meta values for the dates_date key, sort them and take the first one as the start and the last one as the end. So far so good, all is working and the columns display the correct dates.

I want to be able to sort the posts by each of those columns. I'm using the following:

// ... Columns registered as usual using
// "manage_{$event_post_type}_posts_columns" and
// "manage_{$event_post_type}_posts_custom_column"


// Register them as sortable
add_filter(
    "manage_edit-{$event_post_type}_sortable_columns",
    function (array $columns) {
        $columns['start_date'] = 'start_date';
        $columns['end_date'] = 'end_date';
        return $columns;
    }
);

// Sort the post query by the selected column
add_action(
    'pre_get_posts',
    function (\WP_Query $query) {
        $orderby = $query->get('orderby');

        if ($orderby === 'start_date' || $orderby === 'end_date') {
            $meta_query = array(
                'relation' => 'OR',
                array(
                    'key' => $orderby, // start_date or end_date
                ),
                array(
                    'key' => 'dates_date',
                ),
            );

            $query->set('meta_query', $meta_query);
            $query->set('orderby', 'meta_value_num');
        }
    }
);

The reason I have a meta query with two OR clauses is that not all posts use this repeater field, and instead, some use separate start_date and end_date fields. I've just kept it in the example code for completeness.

---

The problem with sorting by "end date" when using the repeater field that saves multiple values for the same dates_date meta_key is that the query seems to take only the first value with this meta_key, and so it sorts them by effectively the start date.

Let's say that I have a post with dates 28 Nov, 29 Nov and 30 Nov; and another post with dates 27 Nov and 1 Dec. Notice that post A starts after post B but finishes before post B. Although the "end date" column correctly displays 30 Nov and 1 Dec respectively, when I click it to sort by end date, it actually puts the second post before the first one when sorting in ascending order, since it seems to compare 28 Nov to 27 Nov (their start dates):

How can I sort by the last meta_value with a given meta_key?

Answer 1

Sorting by multiple meta keys (ref) may be helpful (untested):

add_action( 'pre_get_posts', function ( \WP_Query $query ) {
    $orderby = $query->get( 'orderby' );

    if ( 'start_date' === $orderby || 'end_date' === $orderby ) {
        $meta_query = array(
            'relation' => 'OR',
            'meta_query_1' => array(
               'key' => $orderby, // start_date or end_date
            ),
            'meta_query_2' => array(
                'key' => 'dates_date',
            ),
        );

        $query->set( 'meta_query', $meta_query );

        // Play with this.
        $query->set( 'orderby', array(
            'meta_query_1' => 'ASC',
            'meta_query_2' => 'DESC',
        ) );
    }
} );