WordPress Custom Query + Inner Joins

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!

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.

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

How’d you do it?