How To Write An Inner Join With WP Query

Question

I have a ACF relationship field for a custom post type, properties. Moreover, the content of these properties can either be in English or Spanish. When I use a ACF relationship field to associate properties to a user on the user edit page -- /wp/wp-admin/user-edit.php -- everything works as expected, and I can select from the relationship drop down the properties I want for this user.

My question is this: how can I write a query inside this filter such that only the English properties appear, regardless of the language of the page (English or Spanish) which is set by the WPML toggle? I know how to write such a query in SQL:

SELECT *
FROM wp_2_posts
INNER JOIN wp_2_icl_translations
ON wp_2_icl_translations.element_id = wp_2_posts.id
AND wp_2_icl_translations.language_code = 'en'
WHERE wp_2_posts.post_type = 'properties';

But the filter requires that changes be made to $args which adhere to WP_Query. I do not know how to write the above INNER JOIN on wp_2_icl_translations to only show custom post types properties in English. Can someone please instruct me how so that I can get the acf filter to work the way I need it to?

Answer 1

Ok so the way I was able to just show English properties on the user admin page was with this posts_request hook:

add_filter('posts_request', function($sql, $query) {
  $is_user_edit_page = (
    isset($_SERVER['HTTP_REFERER']) &&
    strpos($_SERVER['HTTP_REFERER'], 'user-edit') !== false
  );

  $is_property_sql  = (strpos($sql, 'property') !== false);

  if ($is_user_edit_page && $is_property_sql) {
    $sql = str_replace("'sp'", "'en'", $sql);
  }

  return $sql;
}, 10, 2);

In this hook I make sure it only runs on the user-edit page and that the sql it's changing relates to the properties. If all those cases are true, then I just replace the Spanish language code with the English one. And as a result, the SQL used to query the properties is forced to only query for English ones.

Thank you @Howdy_McGee for your comments on my question.