360 posts
    Has referred 10+ members Has sold $10,000+ on Envato Market Has collected 50+ items on Envato Market Member of the Envato Team
+8 more
Philo01 Envato team 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! :)

731 posts
    Has referred 500+ members Has sold $125,000+ on Envato Market Has collected 50+ items on Envato Market Elite Author: Sold more than $75,000 on Envato Market
+7 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.

360 posts
    Has referred 10+ members Has sold $10,000+ on Envato Market Has collected 50+ items on Envato Market Member of the Envato Team
+8 more
Philo01 Envato team says

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

731 posts
    Has referred 500+ members Has sold $125,000+ on Envato Market Has collected 50+ items on Envato Market Elite Author: Sold more than $75,000 on Envato Market
+7 more
mordauk says

How’d you do it?

by
by
by
by
by
by