WordPress Awards Sorting Script

<?php

global $wpdb;

// Get all awards
$query = "
    SELECT p.ID, p.post_title,
    MAX(CASE WHEN pm.meta_key = 'awd_year' then pm.meta_value ELSE NULL END) as awd_year,
    MAX(CASE WHEN pm.meta_key = 'awd_sort_cat' then pm.meta_value ELSE NULL END) as awd_sort_cat,
    MAX(CASE WHEN pm.meta_key = 'awd_type' then pm.meta_value ELSE NULL END) as awd_type
    FROM {$wpdb->prefix}posts p
    LEFT JOIN {$wpdb->prefix}postmeta pm ON p.ID = pm.post_id
    WHERE p.post_type = 'awards' AND p.post_status = 'publish'
    GROUP BY p.ID
";
$awards = $wpdb->get_results($query);

// Define your specific order for categories and types
$categories_order = explode("|", get_field('category_order', 'option')); // From ACF Options page
$types_order = explode("|", get_field('type_order', 'option')); // From ACF Options page

// Sort function
usort($awards, function ($a, $b) use ($categories_order, $types_order) {
    // Compare by year
    if ($a->awd_year != $b->awd_year) {
        return $a->awd_year - $b->awd_year;
    }

    // Compare by category
    $a_category_index = array_search($a->awd_sort_cat, $categories_order);
    $b_category_index = array_search($b->awd_sort_cat, $categories_order);
    if ($a_category_index != $b_category_index) {
        return $a_category_index - $b_category_index;
    }

    // Compare by type
    $a_type_index = array_search($a->awd_type, $types_order);
    $b_type_index = array_search($b->awd_type, $types_order);
    if ($a_type_index != $b_type_index) {
        return $a_type_index - $b_type_index;
    }

    return 0;
});