从所有帖子中获取ACF中继器字段值，按子字段排序

Question

我有一个有2个子字段的ACF中继器字段(出版物)。
title和year.
我需要从数据库中选择符合条件的所有帖子的所有标题(假设所有标题都包含'搜索词')，但我需要按年排序的结果(第二子字段)。

这是我用来获取标题的查询。

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);
}

如何按年对结果进行排序?

Answer 1

这是我想到的解决方案。
只有2个SQL查询。

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