sql - How To Write An Inner Join With WP Query

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?

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?

• sql
• plugin-wpml

Share Improve this question asked May 28, 2020 at 15:45 robskrobrobskrob 11166 bronze badges 6

• 1 In short, you cant. WP_Query generates SQL, the filter hook requires you to work with WP_Query. For SQL hooks check out the posts_clauses hook – Howdy_McGee ♦ Commented May 28, 2020 at 16:04
• @Howdy_McGee thank you for your comment. Just to clarify, are you saying that I should not use this filter add_filter('acf/fields/relationship/query/name=properties', function($args, $field, $post_ID) {....}, 10,3) and instead replace that filter with this hook developer.wordpress.org/reference/hooks/posts_clauses ? – robskrob Commented May 28, 2020 at 16:09
• 1 I'm saying the ACF filter hook requires you to work with the WP_Query object which does not accept custom SQL. If you want to write SQL then the ACF hook is not the hook you're looking for. – Howdy_McGee ♦ Commented May 28, 2020 at 16:13
• @Howdy_McGee thank you for the clarification. Indeed, I think I know and understand that I cannot pass raw SQL to WP_Query. I was just hoping that there was a way I could write the $args that is passed to WP_Query, which allows me to only select the posts in English. I was hoping that I could make an $args array that allows me include the wp_2_icl_translations in some way so that I can scope the posts to be only in english. But by what you are telling me, it sounds like there's no DSL for $args that would allow me to write such a join. – robskrob Commented May 28, 2020 at 16:18
• 1 WP_Query works with the built-in WordPress tables. Maybe reach out to WPML and see if they have their own functionality to run that kind of query. – Howdy_McGee ♦ Commented May 28, 2020 at 16:19

 |  Show 1 more comment

1 Answer 1

Sorted by: Reset to default 0

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.