WordPress.org

Forums

[resolved] Querying Taxonomies (20 posts)

  1. Ryan Ball
    Member
    Posted 2 years ago #

    Could anyone give me a push in the right direction? This is for a menu system.

    I currently have Posts and 2 Taxonomies. Based on the ID of a Term in the first Taxonomy, I need to return a list/array of Terms in the second Taxonomy where they share a common Post.

    Does that make sense?

  2. TC.K
    Member
    Posted 2 years ago #

    This is what I looking for, just want to post a similar question and saw your question. Seem I am not the only one that looking for this solution.

    Anybody please.

  3. Ryan Ball
    Member
    Posted 2 years ago #

    I know it can be done via a custom SQL query but my brain just isn't working today and I need to get it done for the client ASAP.

  4. Ryan Ball
    Member
    Posted 2 years ago #

    Does anyone have a better suggestion than this?

    SELECT DISTINCT $wpdb->terms.term_id, $wpdb->terms.name, $wpdb->terms.slug
    FROM $wpdb->terms
    INNER JOIN $wpdb->term_taxonomy ON $wpdb->terms.term_id = $wpdb->term_taxonomy.term_id
    INNER JOIN $wpdb->term_relationships ON $wpdb->term_taxonomy.term_taxonomy_id = $wpdb->term_relationships.term_taxonomy_id
    INNER JOIN $wpdb->posts ON $wpdb->term_relationships.object_id = $wpdb->posts.ID
    INNER JOIN $wpdb->term_relationships tr2 ON $wpdb->posts.ID = tr2.object_id
    INNER JOIN $wpdb->term_taxonomy tt2 ON tr2.term_taxonomy_id = tt2.term_taxonomy_id
    WHERE $wpdb->posts.post_status = 'publish' AND $wpdb->posts.post_type = 'post' AND $wpdb->term_taxonomy.taxonomy = 'tasks' AND tt2.term_id = 592
    ORDER BY $wpdb->terms.name
  5. TC.K
    Member
    Posted 2 years ago #

    This is what I found on wpquestion

    $terms = get_terms(array('tax1', 'tax2'));
    foreach($terms as $term) {
    	echo "<a href='/statistics/term/" . $term->slug . "'";
    	echo "class='" . $term->taxonomy . "'>";
    	echo $term->name;
    	echo "</a>";
    }

    Never tried though.

  6. Ryan Ball
    Member
    Posted 2 years ago #

    Yes, that would give you a list of all terms with the two taxonomies supplied.

    http://codex.wordpress.org/Function_Reference/get_terms

    What I'm attempting to do is only select terms within Taxonmy A that have been assigned to Posts which a specific term in Taxonomy B has also been assigned.

    Should be able to accomplish it with my SQL query, but I was hoping there might be a simpler way.

  7. TC.K
    Member
    Posted 2 years ago #

    I see.

    Are you using hierarchy taxonomies? Or, you using two mutually exclusive taxonomies?

  8. Ryan Ball
    Member
    Posted 2 years ago #

    If only it were that simple. At the insistance of the client, there are two seperate taxonomies. A Post should always be assigned atleast one term from each taxonomy.

    I *think* I have it now. Just slotting it into a plugin and will then test it.

  9. TC.K
    Member
    Posted 2 years ago #

    Really, that's great. Can't wait to see your solution. :)

  10. Ryan Ball
    Member
    Posted 2 years ago #

    if ( !function_exists( 'get_associated_terms' ) ) {
    	function get_associated_terms( $taxonomy_slug, $term_id = 0 ) {
    		global $wpdb;
    
    		$sql = "SELECT DISTINCT $wpdb->terms.term_id, $wpdb->terms.name, $wpdb->terms.slug, $wpdb->terms.term_group, $wpdb->term_taxonomy.term_taxonomy_id, $wpdb->term_taxonomy.taxonomy, $wpdb->term_taxonomy.description, $wpdb->term_taxonomy.parent, $wpdb->term_taxonomy.count
    			FROM $wpdb->terms
    			INNER JOIN $wpdb->term_taxonomy ON $wpdb->terms.term_id = $wpdb->term_taxonomy.term_id
    			INNER JOIN $wpdb->term_relationships ON $wpdb->term_taxonomy.term_taxonomy_id = $wpdb->term_relationships.term_taxonomy_id
    			INNER JOIN $wpdb->posts ON $wpdb->term_relationships.object_id = $wpdb->posts.ID
    			INNER JOIN $wpdb->term_relationships tr2 ON $wpdb->posts.ID = tr2.object_id
    			INNER JOIN $wpdb->term_taxonomy tt2 ON tr2.term_taxonomy_id = tt2.term_taxonomy_id
    			WHERE $wpdb->posts.post_status = 'publish' AND $wpdb->posts.post_type = 'post' AND $wpdb->term_taxonomy.taxonomy = %s AND tt2.term_id = %d
    			ORDER BY $wpdb->terms.name";
    
    		$safe_sql = $wpdb->prepare( $sql, $taxonomy_slug, $term_id );
    
    		$results = $wpdb->get_results( $safe_sql, OBJECT );
    
    		return $results;
    	}
    }
  11. fune
    Member
    Posted 2 years ago #

    @Cornflower Design, can you elaborate how to use ur code?

    Sorry, I am a novice in coding. Hope you don't mind to help me out.

    Thanks

  12. Ryan Ball
    Member
    Posted 2 years ago #

    @fune : Once added to your functions.php file or a plugin, its used in the same way you'd use a built-in function like get_terms() (except my function accepts fewer parameters).

    http://codex.wordpress.org/Function_Reference/get_terms

    It returns an object that can be looped through.

    $terms = get_associated_terms( 'tax1', 256 );
    foreach($terms as $term) {
    	echo "<a href='/tax1/" . $term->slug . "'";
    	echo "class='" . $term->taxonomy . "'>";
    	echo $term->name;
    	echo "</a>";
    }
  13. fune
    Member
    Posted 2 years ago #

    @Cornflower Design, Thank you very much. :)

  14. fune
    Member
    Posted 2 years ago #

    @Cornflower Design, i tried your code but nothing displayed.

    Correct me if I am wrong, in the $terms = get_associated_terms( 'my_tax', 123); isn't it the '123' is the term_id in taxonomy 'my_tax'?

  15. Ryan Ball
    Member
    Posted 2 years ago #

    @fune : The first parameter is the name of the taxonomy you wish to return. The second parameter is the ID of a term within your starting taxonomy.

    Running my example would return an Object containing all terms within the 'tax1' taxonomy which are assigned to posts that also have term 256 assigned to them.

  16. TC.K
    Member
    Posted 2 years ago #

    @Cornflower Design,
    Glad that you finally found solution. However, I also have problem to use your codes.

    I have two custom taxonomy for a post, let's say 'state' and 'city' and I use your codes in the archive page.
    When I landed on the 'state' of New York, I expect to see the 'city' terms that shared the same post.

    So I tweak a bit of your codes:

    $queried_object = get_queried_object();
      $term_id = $wp_query->queried_object->term_id;
      $terms = get_associated_terms( 'city',$term_id);
     foreach($terms as $term) {
     echo "<a href='/city/" . $term->slug . "'";
    	echo "class='" . $term->taxonomy . "'>";
    	echo $term->name;
    	echo "</a>";

    The problem is no result returned, even I tried to insert term_id manually with the actually number without the wp_query.

    Am I correctly implement your codes? Does this codes have use in the content loop or outside the content loop?

  17. Ryan Ball
    Member
    Posted 2 years ago #

    Its a standalone function, so can be used in or outside of the content loop. This is how I'm using it within my functions.php file...

    $subterms = get_associated_terms( 'tasks', $term->term_id );
    
    if ( $subterms ) {
    	$item_output .= '<div><h5>' . __( 'Tasks' ) . '</h5><ul>';
    	foreach ( $subterms as $subterm ) {
    		$item_output .= '<li><a href="' . $term_link . 'tasks/' . $subterm->slug . '">' . $subterm->name . '</a></li>';
    	}
    	$item_output .= '</ul></div>';
    }

    Try removing the WHERE clause from my SQL string and print_r() the resulting Object to your page. Is it returning anything?

  18. TC.K
    Member
    Posted 2 years ago #

    @Cornflower Design,

    I figured the problem! Turn out I forgot to change the $wpdb->posts.post_type = 'post' to the custom post type. Now it works like a charm!!!

    Thanks, You rock!

  19. Ryan Ball
    Member
    Posted 2 years ago #

    @WP_Dummy : To maintain flexibility you could add a third parameter (defaulting to 'post') to the function to allow you to specify the target post type.

    get_associated_terms( $taxonomy_slug, $term_id = 0, $post_type = 'post' )

  20. TC.K
    Member
    Posted 2 years ago #

    @Cornflower Design, wow...never thought of that. thank you very much . :)

Topic Closed

This topic has been closed to new replies.

About this Topic