358 posts
  • Envato Staff
  • Reviewer
  • Sold between 10 000 and 50 000 dollars
  • Has been a member for 4-5 years
  • Bought between 50 and 99 items
  • Contributed a Tutorial to a Tuts+ Site
  • Beta Tester
+4 more
Philo01 Reviewer says

Hi all :)

I’ve been working on a plugin, and I’m kinda stuck on the search function I’m building. Basically I need to grab posts from the database that have specific taxonomies.

I was able to it working with 1 taxonomy, but can’t seem to get it working with more. I’ve searched over the web, and it seems that allot of people have trouble getting this done.

This is the basic query:


// Lets set some fixed ID's for this example
$brand_id = 5;

// Join on postmeta because I need some custom post fields as well
$query = "SELECT DISTINCT p.* FROM wp_posts AS p INNER JOIN wp_postmeta AS p1 ON (p.ID = p1.post_id)";    

// Next let's join the associate taxonomy
$query .= " INNER JOIN wp_term_relationships AS p2 ON (p.ID = p2.object_id)";
$query .= " INNER JOIN wp_term_taxonomy AS p3 ON (p2.term_taxonomy_id = p3.term_taxonomy_id)";

$query .= " WHERE ";

$query .= " AND p3.taxonomy = 'brand' AND p3.term_id = '$brand_id'";

$wpdb->get_results($query, OBJECT);

Now this works! But Let’s say I want to set another specific taxonomy.


// Lets set some fixed ID's for this example
$brand_id = 5;
$model_id = 10;

$query = "SELECT DISTINCT p.* FROM wp_posts AS p INNER JOIN wp_postmeta AS p1 ON (p.ID = p1.post_id)";    

$query .= " INNER JOIN wp_term_relationships AS p2 ON (p.ID = p2.object_id)";
$query .= " INNER JOIN wp_term_taxonomy AS p3 ON (p2.term_taxonomy_id = p3.term_taxonomy_id)";

$query .= " WHERE ";

$query .= " AND p3.taxonomy = 'brand' AND p3.term_id = '$brand_id'";
$query .= " AND p3.taxonomy = 'model' AND p3.term_id = '$model_id'";

$wpdb->get_results($query, OBJECT);

From this point on it stops working. And I would really like to get this working with 3 taxonomies.

Anyone who can give me some advice on this ;) ?

Would really appreciate it!

Thanks! :)

730 posts
  • Elite Author
  • Attended a Community Meetup
  • Has been a member for 4-5 years
  • Sold between 100 000 and 250 000 dollars
  • Bought between 50 and 99 items
  • Exclusive Author
  • Most Wanted Bounty Winner
+2 more
mordauk says
This is code I got from Stack Overflow.
SELECT * 
FROM $wpdb->posts
LEFT JOIN $wpdb->term_relationships ON($wpdb->posts.ID = $wpdb->term_relationships.object_id)
LEFT JOIN $wpdb->term_taxonomy ON($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
LEFT JOIN $wpdb->terms ON($wpdb->term_taxonomy.term_id = $wpdb->terms.term_id)
WHERE $wpdb->posts.post_type = 'post' 
AND $wpdb->posts.post_status = 'publish'
AND $wpdb->term_taxonomy.taxonomy = 'technologies'
AND $wpdb->terms.slug = 'php' OR $wpdb->terms.slug = 'css'
ORDER BY $wpdb->posts.post_date DESC

That should get the posts from a taxonomy of “technologies” with values of “php” or “css”.

If you’re talking about two distinct taxonomies (not two values within same tax), then try this:

SELECT * 
FROM $wpdb->posts
LEFT JOIN $wpdb->term_relationships ON($wpdb->posts.ID = $wpdb->term_relationships.object_id)
LEFT JOIN $wpdb->term_taxonomy ON($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
LEFT JOIN $wpdb->terms ON($wpdb->term_taxonomy.term_id = $wpdb->terms.term_id)
WHERE $wpdb->posts.post_type = 'post' 
AND $wpdb->posts.post_status = 'publish'
AND $wpdb->term_taxonomy.taxonomy = 'tax1'
OR $wpdb->term_taxonomy.taxonomy = 'tax2'
ORDER BY $wpdb->posts.post_date DESC

Something along those lines.

358 posts
  • Envato Staff
  • Reviewer
  • Sold between 10 000 and 50 000 dollars
  • Has been a member for 4-5 years
  • Bought between 50 and 99 items
  • Contributed a Tutorial to a Tuts+ Site
  • Beta Tester
+4 more
Philo01 Reviewer says

Forgot to reply that I already solved it ;) Thanks for the reply though!

730 posts
  • Elite Author
  • Attended a Community Meetup
  • Has been a member for 4-5 years
  • Sold between 100 000 and 250 000 dollars
  • Bought between 50 and 99 items
  • Exclusive Author
  • Most Wanted Bounty Winner
+2 more
mordauk says

How’d you do it?

by
by
by
by
by
by