Get ACF repeater field values from all posts, sorted by a sub-field

Question

I have an ACF repeater field (publications) with 2 sub-fields.
title and year.
I need to select from the database all titles from all posts matching a condition (let’s say all titles which include ‘search-term’) but I need the result sorted by the year (the 2nd sub-field).

This is the query I use to fetch the titles.

    function get_search_results(): array 
    {
        global $wpdb;

        $sql = "SELECT pm.meta_value title, pm.post_id post
                FROM {$wpdb->posts} p
                JOIN {$wpdb->postmeta} pm ON p.ID = pm.post_id
                WHERE p.post_status = 'publish'
                      AND pm.meta_key LIKE 'publication_%_title'
                      AND pm.meta_value LIKE '%search-term%';";

        return $wpdb->get_results($sql, ARRAY_A);
    }

How can I sort the results by the year?

Answer 1

This is the solution I came up with.
Only 2 SQL queries.

 function get_search_results(): array 
    {
        global $wpdb;

        // Get publication titles.
        $sql = "SELECT pm.meta_key, pm.meta_value title, pm.post_id researcher
                FROM {$wpdb->posts} p
                JOIN {$wpdb->postmeta} pm ON p.ID = pm.post_id
                WHERE p.post_status = 'publish'
                      AND pm.meta_key LIKE 'publication_%_title'
                      AND pm.meta_value LIKE '%search-term%';";

        $titles = $wpdb->get_results($sql, ARRAY_A);

        // Get list of post IDs.
        $researcher_ids = implode(', ', array_unique(array_column($titles, 'researcher')));

        // Get publication years.
        $sql = "SELECT REPLACE(pm.meta_key,'_year','_title') AS meta_key, pm.meta_value year, pm.post_id researcher
            FROM {$wpdb->posts} p
            JOIN {$wpdb->postmeta} pm ON p.ID = pm.post_id
            WHERE p.post_status = 'publish'
                  AND pm.meta_key LIKE 'publication_list_%_year'
                  AND pm.post_id IN ($researcher_ids);";

        $years_raw = $wpdb->get_results($sql, ARRAY_A);
        $years = [];

        // Reformat the publication years.
        foreach ($years_raw as $year) {
            $years[$year['researcher'] . '__' . $year['meta_key']] = $year['year'];
        }

        // Add the year field to each title.
        foreach ($titles as &$title) {
            $title['year'] = $years[$title['researcher'] . '__' . $title['meta_key']];
        }

        // Sort the array by the inner year value.
        usort($titles, 'sortByInnerYear');

        return $titles;
    }

    function sortByInnerYear($a, $b): int
    {
        return $b['year'] <=> $a['year'];
    }