1

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?

  • Can you explain better what the query is supposed to do? "Search posts based on taxonomy terms" seems quite easy but not your code. – cybmeta Dec 28 '14 at 8:57
  • I am querying posts based on taxonomy terms. I dont want to use WP_Query. I want to filter the search query by using the posts_where filter hook and SQL query. – Gixty Dec 28 '14 at 9:05
4

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) ";
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.