Get repeater sub-field values, sorted by a 2nd subfield

• baras

• October 24, 2021 at 9:05 am

Hi,

I have a tricky issue to solve.

I have a 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?
Thanks a lot!

• baras

• October 26, 2021 at 4:14 am

Hi,

I posted the solution I came up with here.
Hope it helps someone.