Support » Plugins » check multiple taxonomies in WHERE statement of SELECT Query

  • Resolved 10sexyapples


    I’m trying to modify some code for finding related posts, efficient related posts to be exact, and I’m getting stuck on the WHERE statement. I changed WHERE t_t.taxonomy = ‘post_tag’, to WHERE t_t.taxonomy = ‘art’, which is one of my custom taxonomies, but, I need to check through all of my custom taxonomies in the WHERE statement, not just one, for all of the terms that are assigned to the current post. Can anyone help me to better understand how to go about forming the syntax on this?
    Thanks in advance~

Viewing 4 replies - 1 through 4 (of 4 total)
  • Didn’t test this much, but try:

    $query="SELECT $wpdb->posts.* FROM $wpdb->posts
    INNER JOIN $wpdb->term_relationships ON ($wpdb->posts.ID = $wpdb->term_relationships.object_id)
    INNER JOIN $wpdb->term_taxonomy ON ($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
    WHERE 1=1  AND $wpdb->term_taxonomy.taxonomy IN ('post_tag','art','love','genre')
    AND $wpdb->posts.post_type = 'post'
    AND ($wpdb->posts.post_status = 'publish')
    GROUP BY $wpdb->posts.ID
    ORDER BY $wpdb->posts.post_date DESC";
      foreach ($results as $result) {
    echo "<pre>"; print_r($result); echo "</pre>";

    Excellent. Thank you so much. I can’t wait until this is second nature to me. I just have to keep reading my php books. 😉

    I just came to the forum to share some code relating to multiple taxonomies and similar posts… here’s what I wrote up this afternoon:

    SELECT s.object_id, COUNT(*) AS matches FROM wp_posts x, wp_term_relationships s INNER JOIN (SELECT r.term_taxonomy_id FROM wp_term_relationships r, wp_term_taxonomy t WHERE r.object_id = '$id' AND r.term_taxonomy_id = t.term_taxonomy_id AND (t.taxonomy = 'style' OR t.taxonomy = 'artist' OR t.taxonomy = 'label')) p ON p.term_taxonomy_id = s.term_taxonomy_id WHERE object_id != '$id' AND x.ID = s.object_id AND x.post_status = 'publish' GROUP BY s.object_id HAVING matches > 1 ORDER BY matches DESC, RAND() LIMIT 3

    Xerotones code works perfect! How is it possible to get an array with all the tax-terms which are matching the original post’s terms into the result object?

    The object foreach related post looks like this:

    stdClass Object
        [object_id] => 458
        [matches] => 4

    and I’d like it to look like this:

    stdClass Object
        [object_id] => 458
        [matches] => 4
    	[matching_terms] => Array(
    							[0] => 146
    							[1] => 155
    							[2] => 190
    							[3] => 213

    Can this be done? Thank You for help!

Viewing 4 replies - 1 through 4 (of 4 total)
  • The topic ‘check multiple taxonomies in WHERE statement of SELECT Query’ is closed to new replies.