How to use orderby on meta_value when using Pods custom database table storage

As with any custom tables in your database, you can override the SQL that WP_Query ends up using.

For this use case, I’d suggest not using WP_Query’s arguments and building your own function to do this instead as it could simplify the logic and complexity.

<?php

add_filter( 'posts_clauses', 'my_custom_wp_query_posts_clauses', 10, 2 );

/**
 * Custom WP_Query clauses to allow joining custom tables.
 *
 * @param array    $clauses The post clauses.
 * @param WP_Query $query   The WP_Query object.
 *
 * @return array The filtered post clauses.
 */
function my_custom_wp_query_posts_clauses( array $clauses, WP_Query $query ): array {
    // Only override for the main query that is not on a feed/admin page and is on the post type archive.
    if (
        ! $query->is_main_query()
        || $query->is_feed()
        || is_admin()
        || ! $query->is_post_type_archive( 'xyz_cpt' )
    ) {
        return $clauses;
    }

    global $wpdb;

    // Add the custom join with the wp_pods_PODNAME table.
    $clauses['join'] .= "
        LEFT JOIN `{$wpdb->prefix}pods_xyz_cpt` AS `pods_table`
            ON `pods_table`.`id` = `{$wpdb->posts}`.`ID`
    ";

    // Set our custom orderby.
    $orderby = '`pods_table`.`your_field_name`';

    // If the orderby is not already set then add ours to the front, otherwise set the orderby to ours.
    if ( ! empty( $clauses['orderby'] ) ) {
        $clauses['orderby'] = $orderby . ', ' . $clauses['orderby'];
    } else {
        $clauses['orderby'] = $orderby;
    }

    return $clauses;
}

If you instead needed to add custom WHERE stuff, you’d just add whatever SQL you wanted to $clauses['where'] too.