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?

Check this answer - https://stackoverflow.com/questions/16319363/custom-select-query-wordpress-order-by-meta-key-value — Snuffy, Oct 25 '21 at 13:37
Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. — Community, Oct 25 '21 at 14:30
Thanks everybody. To answer this question you really have to be familiar with ACF and the way it stores repeater field information in the database. Then the question (and the problem) will be clear. — Baras, Oct 26 '21 at 06:31

score 1 · Accepted Answer · edited Nov 02 '21 at 10:38

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'];
    }

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

1 Answers1