How to write inner join using posts_clauses?

Question

Can someone please show me how I can achieve the same result with this 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';

Using posts_clauses ? In other words I'd like a posts_clauses filter to perform the same query as the one shown above in SQL.

I am trying to query posts that are only in English.

Answer 1

First, his filter is explained in the manual and is found in the code. If you have questions about hooks and functions, the manual and code is a good place to start looking for answers. The post_clauses hook is passed the associative array to filter, so just manipulate the where and join indices as per your needs. (As per the manual and code, this filter cannot modify table name.)

Also, this kind of question has already been answered, and I'd like to direct your attention to that discussion for coding examples.

UPDATE: OK, @robskrob, here you go! Again, this will not initialize the query object to be blank nor will it filter the table name.

add_filter('post_clauses', function($clauses){
    $clauses['join'] .= " 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'";
    $clauses['where'] .= " AND wp_2_posts.post_type = 'properties'";
    return $clauses;
});