WordPress.org

Ready to get started?Download WordPress

Forums

[resolved] check multiple taxonomies in WHERE statement of SELECT Query (5 posts)

  1. 10sexyapples
    Member
    Posted 3 years ago #

    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~

  2. MichaelH
    Member
    Posted 3 years ago #

    Didn't test this much, but try:

    <?php
    $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";
    $results=$wpdb->get_results($query);
      foreach ($results as $result) {
    echo "<pre>"; print_r($result); echo "</pre>";
    }
    ?>
  3. 10sexyapples
    Member
    Posted 3 years ago #

    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. ;-)

  4. Xerotone
    Member
    Posted 3 years ago #

    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

  5. rogersabboes
    Member
    Posted 3 years ago #

    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!

Topic Closed

This topic has been closed to new replies.

About this Topic