How to use filter hook posts_join for querying taxonomy terms in posts_where?

Question

For the last few hours, I have been trying to use the filter hooks posts_where and posts_join to search for posts based on taxonomy terms, in this case, the taxonomy is 'category' and the term is 'politics'

This is what I tried and resulted in a empty query:

This line is inside the hook posts_where

$where .= " OR ($wpdb->term_taxonomy.taxonomy IN ('category') AND $wpdb->terms.name = 'politics')";

And this is what I used to join the different tables:

$join .= " LEFT JOIN $wpdb->postmeta ON $wpdb->posts.ID = $wpdb->postmeta.post_id ";
$join .= " LEFT JOIN $wpdb->term_relationships ON ($wpdb->posts.ID = $wpdb->term_relationships.object_id) ";
$join .= " LEFT JOIN $wpdb->term_taxonomy ON ($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id) ";
$join .= " LEFT JOIN $wpdb->terms ON($wpdb->term_taxonomy.term_id = $wpdb->terms.term_id) ";

I did come up with an alternative that works but I want to know what I am doing wrong with the previous code and how to make it work.

The working alternative is this (this is without using posts_join hook):

$search .= " OR ($wpdb->posts.ID IN( 
    SELECT $wpdb->term_relationships.object_id as post_id 
    FROM $wpdb->term_taxonomy,  $wpdb->terms, $wpdb->term_relationships 
    WHERE $wpdb->term_taxonomy.term_id =  $wpdb->terms.term_id
    AND $wpdb->term_relationships.term_taxonomy_id =  $wpdb->term_taxonomy.term_taxonomy_id
    AND $wpdb->term_taxonomy.taxonomy in ('category')
    AND ($wpdb->terms.name LIKE 'politics')  
    )
)";

So, how can I use posts_where and posts_join hooks to query taxonomy terms?

Answer 1

Well, after trying different things, I came up with the following.

When we use posts_join there is no need to add the prefix $wpdb-> plus the table being queried. We just need the column name. In this case, we just need name and taxonomy, like this:

BEFORE

$where .= " OR ($wpdb->term_taxonomy.taxonomy IN ('category') AND $wpdb->terms.name LIKE 'politics')";

AFTER

$where .= " OR ( taxonomy IN ('category') AND name LIKE 'politics')";

It is good to point out that as queries get more complex, aliases should be used.

Using aliases looks like this:

For posts_where hook:

$where .= " OR (wtt.taxonomy IN ('category') AND wt.name LIKE 'politics' )";

For posts_join hook:

$join .= " LEFT JOIN $wpdb->term_relationships as wtr ON ($wpdb->posts.ID = wtr.object_id) ";
$join .= " LEFT JOIN $wpdb->term_taxonomy as wtt ON (wtr.term_taxonomy_id = wtt.term_taxonomy_id) ";
$join .= " LEFT JOIN $wpdb->terms as wt ON(wtt.term_id = wt.term_id) ";