Enhance Advanced Custom Fields (ACF) Relationship Search Field for WordPress

functions.php

<?php

/**
 * Enhance ACF Relationship field search to include title, a specific custom field, and publish date.
 *
 * Replace 'your_relationship_field_name' with the ACF field name you're targeting.
 * Replace 'your_post_type' with the ACF CPT you are targeting.
 * Replace 'custom_field_key' with the meta_key you want to search.
 */
 
add_filter('acf/fields/relationship/query/name=your_relationship_field_name', function ($args, $field, $post_id) {
    global $wpdb;

    // Only apply custom search when a query string is provided
    if (empty($args['s'])) {
        return $args;
    }

    $search = esc_sql(trim($args['s']));

    // Query matching post IDs based on title, custom field, and publish date
    $matched_ids = $wpdb->get_col("
        SELECT DISTINCT p.ID
        FROM {$wpdb->posts} p
        LEFT JOIN {$wpdb->postmeta} pm ON p.ID = pm.post_id
        WHERE p.post_type = 'your_post_type'
          AND p.post_status = 'publish'
          AND (
              p.post_title LIKE '%{$search}%'
              OR (pm.meta_key = 'custom_field_key' AND pm.meta_value LIKE '%{$search}%')
              OR DATE_FORMAT(p.post_date, '%Y-%m-%d') LIKE '%{$search}%'
          )
        LIMIT 100
    ");

    // Tell ACF to use only the matched post IDs
    $args['s'] = ''; // prevent double search filtering
    $args['orderby'] = 'post__in';
    $args['post__in'] = !empty($matched_ids) ? $matched_ids : [0];

    return $args;
}, 10, 3);

Created with some help from the sample code from here:
Intercept Advanced Custom Fields (ACF) Relationship Field Searches