I’m struggling to understand how I can use pre_get_post when the post’s metadata is not stored in the wp_postmeta table. I use Pods (https://pods.io) to create custom post types with custom fields, stored using the plugins Table Storage option.

My desired outcome is to use orderby on the meta_value stored in the custom xyz_custom_table table.

The following xyz_order_post_by_meta function is how I would do this with posts that store metadata in the wp_postmeta table. But this will not work with custom database tables.

function xyz_order_post_by_meta($query)
{
  if (
    $query->is_main_query() && !$query->is_feed() && !is_admin() && $query->is_post_type_archive("xyz_cpt")
  ) {
    $query->set("meta_key", "xyz_custom_meta");
    $query->set("orderby", "meta_value");
    $query->set("order", "ASC");
  }
}
add_action("pre_get_posts", "xyz_order_post_by_meta", 9999);

Am I right in thinking I need to run a custom SQL query to get the value for meta_value to include in the above? If so, how would I write this? I’ve tried the following, which returns a list of the meta_value for each post. But I’m not sure how to write this as the array I think I need for $query->set("orderby", "meta_value"); to give me the desired outcome?

global $wpdb;
    $table_name = "xyz_custom_table";
    $retrieve_data = $wpdb->get_results("SELECT * FROM $table_name");

    foreach ($retrieve_data as $retrieved_data) {
      echo $retrieved_data->xyz_custom_meta;
    }