WordPress.org

1. jasongeiger
   Member
   Posted 3 years ago #
   

   I've noticed there is no native way to retrieve tags based on the category. I've seen a few plugins that do something similar but don't this. There have also been been a few snippets posted that kinda do it but either show duplicates of tags, or seem too labor intensive.

   In short has anyone done a db query as follows:

   select category
   from category select posts
   from posts select post tags
   make array of unique post tags (id, name, slug)

   I've started playing with the query function but as I'm learning and shooting in dark.

   Any help, even a quick tut on how to run a query would be helpful.

2. Electric Studio
   Member
   Posted 3 years ago #
   

   Bump, exactly what I'm looking for too. I want to grab the Meta data from multiple posts in one category, then loop it out nicely at the bottom of a page/in a sidebar etc.

   Any friendlies out there?

3. Electric Studio
   Member
   Posted 3 years ago #
   

   OK, this is what i have done, much more basic than i thought:

   <ul>
   <?php global $post;
   		     $myposts = get_posts('numberposts=-1&offset=0&category=13&order=DESC');
   		     foreach($myposts as $post) :
   	?>
   	<li>
       <?php if(get_post_meta($post->ID, 'Event Name', true)): ?><?php echo get_post_meta($post->ID, 'Event Name', true); ?><br /><?php endif; ?>
   	<?php if(get_post_meta($post->ID, 'Event Location', true)): ?><?php echo get_post_meta($post->ID, 'Event Location', true); ?><br /><?php endif; ?>
       <?php if(get_post_meta($post->ID, 'Event Date Day', true)): ?><?php echo get_post_meta($post->ID, 'Event Date Day', true); ?><br /><?php endif; ?>
       <?php if(get_post_meta($post->ID, 'Event Date Month', true)): ?><?php echo get_post_meta($post->ID, 'Event Date Month', true); ?><br /><?php endif; ?>
       <?php if(get_post_meta($post->ID, 'Event Date Year', true)): ?><?php echo get_post_meta($post->ID, 'Event Date Year', true); ?><br /><?php endif; ?>
       <?php if(get_post_meta($post->ID, 'Event Time', true)): ?><?php echo get_post_meta($post->ID, 'Event Time', true); ?><br /><?php endif; ?>
   	</li>
   	<?php endforeach; ?>
   </ul>

   Hopefully this will help someone.

4. jasongeiger
   Member
   Posted 3 years ago #
   

   Electric Studio, that's not exactly what I was looking for but it was good stepping stone.

   <?php
   	query_posts('category_name=work');
   	if (have_posts()) : while (have_posts()) : the_post();
           $posttags = get_the_tags();
   		if ($posttags) {
   			foreach($posttags as $tag) {
   				$all_tags_arr[] = $tag -> name; //USING JUST $tag MAKING $all_tags_arr A MULTI-DIMENSIONAL ARRAY, WHICH DOES WORK WITH array_unique
   			}
   		}
   	endwhile; endif; 
   
   	$tags_arr = array_unique($all_tags_arr); //REMOVES DUPLICATES
   	echo '<pre>'.print_r($tags_arr, true).'</pre>'; //OUTPUT FINAL TAGS FROM CATEGORY
   
   ?>

   This does the job, except it would be nice to have $tags_arr as a multi-dimensional array but that does work with array_unique.

   Still my question is, if you have say 100 posts in a category, is doing query like this the most efficient way of getting these results.

5. Minkowski
   Member
   Posted 3 years ago #
   

   I too would like to find a more elegant solution to this.

6. s_ha_dum
   Member
   Posted 3 years ago #
   

   I'd probably write the query from scratch. query_posts is pulling a lot of data you don't need. But that's not a query for the faint of heart...

7. Mark / t31os
   Moderator
   Posted 3 years ago #
   

   So just to be clear the aim here is to grab post tags that exist for posts in one particular category?..

   I think we can proberly manage that.

   1. Grab IDs for all posts in particular category
   2. Grab post tags for each post
   3. Remove duplicates
   4. Display data

   I think what's important is to not grab all post data when you only need the IDs.

   Give me a little while, i think i might be able to knock something up..

8. Mark / t31os
   Moderator
   Posted 3 years ago #
   

   Can someone just clarify if you need the posts or just the tags?..

   If you need the posts to, then you might aswell build array items for each post, then at the end of all the posts, the array is finished, just remove dupes, then display the data..

   If you don't need posts, and just the post_tags, then this could be refined into a single stand-alone query, but if you're grabbing posts to there's no reason you can't just build the array as you display each post.. which is what you've got posted above... except that you've used query_posts simply to loop over each post in that category, but then you don't use any of the data, you're simplying using it in order to run get_the_tags, which of course is a little inefficient.

   Do you want post data or simply just the tags and nothing but the tags? (which it looks like was the intention).

9. jasongeiger
   Member
   Posted 3 years ago #
   

   This what I've come up with. I think apljdi, is right about custom query. This still seems a bit excessive.

   <?php
           $project_query = query_posts('category_name=projects');
           while (have_posts()) : the_post();
               $posttags = get_the_tags();
               if ($posttags) {
                   foreach($posttags as $tag) {
                       $all_tags_arr[] = $tag -> name; //USING JUST $tag MAKING $all_tags_arr A MULTI-DIMENSIONAL ARRAY, WHICH DOES WORK WITH array_unique
                   }
               }
           endwhile;
       ?>
       <?php if ( is_array($all_tags_arr) && count($all_tags_arr) > 0 ): ?>
   <?php
       $tags_arr = array_unique($all_tags_arr); //REMOVES DUPLICATES
       foreach( $tags_arr as $tag ):
           $el = get_term_by('name', $tag, 'post_tag');
   		$arr[] = '"tag-'.$el->slug.'"';
       ?>
       <span><a href="#<?php echo $el->slug; ?>" id="taglink-tag-<?php echo $el->slug; ?>" rel="tag-<?php echo $el->slug; ?>"><?php echo $el->name; ?></a> <span class="slash">//</span></span>
   <?php endforeach; ?>

10. jasongeiger
    Member
    Posted 3 years ago #
    

    The goal of this was to be able to batch tags based on the category to develop a category specific list of tags, which in turn linked to that tags filtered archive.

11. Mark / t31os
    Moderator
    Posted 3 years ago #
    

    I suppose the question is whether a get_results is more efficient then query_posts, if not then a custom joined query isn't going to be mean anything at this point and refining what you have would be best...

    Of course get_results in theory should be lighter and quicker, so perhaps a query that joins the various fields together will give the best results, but i'll have to spend some time looking over the tables (figure out the joins - mysql is not my strong suit).

    I'll see if i can write the query for you when i get some time to test it.... joins are a hair puller... lol..

12. Mark / t31os
    Moderator
    Posted 3 years ago #
    

    Wow this is a tricky one, post_tags and categories sit in the same table...

    Just trying to get the query right first, here's what i have so far if anyone thinks they can help (just in phpmyadmin until i have the right query).

    SELECT DISTINCT wp_posts.ID, wp_term_taxonomy.*, wp_terms.*
    FROM wp_posts, wp_term_relationships, wp_term_taxonomy
    JOIN wp_terms ON wp_term_taxonomy.term_taxonomy_id = wp_terms.term_id
    WHERE wp_posts.ID = wp_term_relationships.object_id
    AND wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
    AND wp_term_taxonomy.taxonomy = 'post_tag'
    LIMIT 0 , 300

    Anyone spot any immediate problems? .. Of course i'm still working on figuring out how to ensure posts match a set category, it's taken me a little while to get this far, not bad for a single query..

    Query took 0.0065 sec

    Mind you only 30 results on my install, but nice and fast, just need to figure out the rest of the query...

    Could really do with some help from someone who's written joins before. The above WHERE's act as JOINS, but i put a regular JOIN in to... it's a little beyond me if i'm to be frank, but i'll give it a shot nonetheless.... :)

    The real killer is how to join up the statement when post tags and categories sit in the same table, i can't quite wrap my head around how i can limit to one category, whilst still grabbing info on other entries in that very same table... ** just need a fresh set of eyes i think **

13. kelseydamas
    Member
    Posted 3 years ago #
    

    This query seemed to work for me. In this case 'Lectures' is the name of the category and the column 'tag' will have all the tags in this category

    SELECT DISTINCT terms2.name as tag
    FROM
    	wp_posts as p1
    	LEFT JOIN wp_term_relationships as r1 ON p1.ID = r1.object_ID
    	LEFT JOIN wp_term_taxonomy as t1 ON r1.term_taxonomy_id = t1.term_taxonomy_id
    	LEFT JOIN wp_terms as terms1 ON t1.term_id = terms1.term_id,
    
    	wp_posts as p2
    	LEFT JOIN wp_term_relationships as r2 ON p2.ID = r2.object_ID
    	LEFT JOIN wp_term_taxonomy as t2 ON r2.term_taxonomy_id = t2.term_taxonomy_id
    	LEFT JOIN wp_terms as terms2 ON t2.term_id = terms2.term_id
    WHERE
    	t1.taxonomy = 'category' AND p1.post_status = 'publish' AND terms1.name = 'Lectures' AND
    	t2.taxonomy = 'post_tag' AND p2.post_status = 'publish'
    	AND p1.ID = p2.ID

14. Mark / t31os
    Moderator
    Posted 3 years ago #
    

    Perfect example, thank you kelsey...

    Clearly someone who knows their joins.. ;) Worked flawlessly first time..

15. toplink
    Member
    Posted 3 years ago #
    

    excellent work kelseydamas
    thank you for it

16. john.andrews
    Member
    Posted 3 years ago #
    

    Great code input everyone, it's exactly what I needed for a WP I'm working on. I took it a bit further, perhaps someone who hasn't implemented will find this useful.

    In your theme's functions.php insert the following function:

    function get_category_tags($args) {
    	global $wpdb;
    	$tags = $wpdb->get_results
    	("
    		SELECT DISTINCT terms2.term_id as tag_id, terms2.name as tag_name, null as tag_link
    		FROM
    			wp_posts as p1
    			LEFT JOIN wp_term_relationships as r1 ON p1.ID = r1.object_ID
    			LEFT JOIN wp_term_taxonomy as t1 ON r1.term_taxonomy_id = t1.term_taxonomy_id
    			LEFT JOIN wp_terms as terms1 ON t1.term_id = terms1.term_id,
    
    			wp_posts as p2
    			LEFT JOIN wp_term_relationships as r2 ON p2.ID = r2.object_ID
    			LEFT JOIN wp_term_taxonomy as t2 ON r2.term_taxonomy_id = t2.term_taxonomy_id
    			LEFT JOIN wp_terms as terms2 ON t2.term_id = terms2.term_id
    		WHERE
    			t1.taxonomy = 'category' AND p1.post_status = 'publish' AND terms1.term_id IN (".$args['categories'].") AND
    			t2.taxonomy = 'post_tag' AND p2.post_status = 'publish'
    			AND p1.ID = p2.ID
    		ORDER by tag_name
    	");
    	$count = 0;
    	foreach ($tags as $tag) {
    		$tags[$count]->tag_link = get_tag_link($tag->tag_id);
    		$count++;
    	}
    	return $tags;
    }

    In your theme document call the function as follows. Notice it accepts multiple category id's:

    $args = array(
    	'categories'				=> '12,13,14'
    );
    $tags = get_category_tags($args);

    This will return an array that you could do the following with:

    $content .= "<ul>";
    foreach ($tags as $tag) {
    	$content .= "<li><a href=\"$tag->tag_link\">$tag->tag_name</a></li>";
    }
    $content .= "</ul>";
    echo $content;

17. MichaelH
    Member
    Posted 3 years ago #
    

    Just adding a link to this plugin as it can handle tag/category 'intersections':

    http://wordpress.org/extend/plugins/tdo-tag-fixes/

18. kelseydamas
    Member
    Posted 3 years ago #
    

    Here's the same query as I posted above but it includes an extra column for the number of posts using that tag. This returns the top 20 tags in a given category.

    SELECT DISTINCT terms2.name as name, terms2.slug as slug, COUNT(terms2.slug) as numposts
                    FROM
                        wp_posts as p1
                        LEFT JOIN wp_term_relationships as r1 ON p1.ID = r1.object_ID
                        LEFT JOIN wp_term_taxonomy as t1 ON r1.term_taxonomy_id = t1.term_taxonomy_id
                        LEFT JOIN wp_terms as terms1 ON t1.term_id = terms1.term_id,
    
                        wp_posts as p2
                        LEFT JOIN wp_term_relationships as r2 ON p2.ID = r2.object_ID
                        LEFT JOIN wp_term_taxonomy as t2 ON r2.term_taxonomy_id = t2.term_taxonomy_id
                        LEFT JOIN wp_terms as terms2 ON t2.term_id = terms2.term_id
                    WHERE
                        t1.taxonomy = 'category' AND p1.post_status = 'publish' AND terms1.name = '$categoryName' AND
                        t2.taxonomy = 'post_tag' AND p2.post_status = 'publish'
                        AND p1.ID = p2.ID
                    GROUP BY terms2.slug
                    ORDER BY numposts DESC
                    LIMIT 20

19. Mark / t31os
    Moderator
    Posted 3 years ago #
    

    Kelsey the count is already in the count field(column) in the taxonomy table, would it not be quicker to just select that field... here's the query snippet i've been using...
    

    $category_tags = $wpdb->get_results("
    SELECT DISTINCT
    	terms2.term_id as tag_ID,
    	terms2.name as tag_name,
    	t2.count as posts_with_tag
    FROM
    	$wpdb->posts as p1
    	LEFT JOIN $wpdb->term_relationships as r1 ON p1.ID = r1.object_ID
    	LEFT JOIN $wpdb->term_taxonomy as t1 ON r1.term_taxonomy_id = t1.term_taxonomy_id
    	LEFT JOIN $wpdb->terms as terms1 ON t1.term_id = terms1.term_id,
    
    	$wpdb->posts as p2
    	LEFT JOIN $wpdb->term_relationships as r2 ON p2.ID = r2.object_ID
    	LEFT JOIN $wpdb->term_taxonomy as t2 ON r2.term_taxonomy_id = t2.term_taxonomy_id
    	LEFT JOIN $wpdb->terms as terms2 ON t2.term_id = terms2.term_id
    WHERE (
    	t1.taxonomy = 'category' AND
    	p1.post_status = 'publish' AND
    	terms1.term_id = '$varcat' AND
    	t2.taxonomy = 'post_tag' AND
    	p2.post_status = 'publish' AND
    	p1.ID = p2.ID
    	)
    ");

20. whipd09
    Member
    Posted 3 years ago #
    

    Hey guys, I'm looking for something VERY similar to this.

    I need the tags at the end of a post (single page) to ONLY link to a tag archive of posts that are also in the SAME parent category as that post.

    Any Ideas?

21. MarjWyatt
    Member
    Posted 3 years ago #
    

    I read this thread with interest. What I want to do is somewhat hard to explain and is confounded further by the fact that my pal is using Thesis, which goes about WordPress in a very different way. ~sigh~

    Scenario is this:
    1) A company profile post exists in profiles category and company name profile without any tag.

    2) Regular news updates will be posted to the site that are categorized in a News category and also categorized in the company category. Using tags that match the company name seemed to be the best way to integrate the content.

    So we have 3 categories involved. Profile, News, and Company. When visitors choose the profile content, it is a single post that we want to augment with content from the News Category relating to that Company.

    If you're still reading and understanding, I totally appreciate you!

    I admit that I am not SQL savvy, although I have administered databases in the past. The problem is ... that was over 15 years ago! I would qualify my skill level is being "dangerous" but not foolish.

    If worked out the following query which kind of does what I want:

    SELECT post_title, post_date, post_content, name AS cat_name
    FROM 	wp_posts p,
    	wp_terms t,
    	wp_term_relationships r,
    	wp_term_taxonomy tt
    WHERE	p.post_status='publish' AND
    	tt.taxonomy='post_tag' AND
    	tt.description=t.name AND
    	p.id=r.object_id AND
    	r.term_taxonomy_id=tt.term_taxonomy_id AND
    	tt.term_id=t.term_id;

    The problem is this, I can't figure out how to ask "if" before submitting the query and this query returns content that is unrelated to the company because the conditions are met and there is nothing to exclude non-company related News.

    Help me please. This solution is in the critical path.

22. alukeonlife
    Member
    Posted 3 years ago #
    

    I follow the logic above, but I'd love a bit of help on implementation:

    If I add the "function get_category_tags" code above to my functions.php is there a way I can modify the tag cloud widget to display the current category if i'm on a category page or all if not?

    IE in psuedo-code:

    If (on a category page) then
    tag cloud called using get_category_tags (with current catID)
    else
    normal tag cloud code

    The aim is to only show tags relating to products in the current category when on a category page.

23. Mark / t31os
    Moderator
    Posted 3 years ago #
    

    @MarjWyatt - What do you need to check before the query?

    @alukeonlife - You could do something like..
    

    if(is_category()) {
      // Category tag function
    }
    else {
      // Regular tag cloud,  wp_tag_cloud (or whatever)
    }

24. huaer123
    Blocked
    Posted 3 years ago #
    

    I need the tags at the end of a post (single page) to ONLY link to a tag archive of posts that are also in the SAME parent category as that post.

25. Ally
    Member
    Posted 3 years ago #
    

    Get Tags specific to Category: you can get it by installing Simple Tags plugin and calling st_tag_cloud('category=YOUR_CAT_ID') on your category pages.

26. saku4388
    Member
    Posted 3 years ago #
    

    Hi jasongeiger

    Thanks for the code i like it but i want to know one more thing.

    i m able to extract all tags within the category but what if someone wants custom taxonomy with the categories

    actually i have two section on my blog

    Brand Name - which is handled by tags
    Types - which is handles by custom taxonomy named TYPE

    so i want to list all the types within the category

    could you please provide me the modified code

    Thanks

27. cybersniper
    Member
    Posted 3 years ago #
    

    Get Tags specific to Category: you can get it by installing Simple Tags plugin and calling st_tag_cloud('category=YOUR_CAT_ID') on your category pages.

    Sounds good ... I tried your suggestion but unfortunately it won't work for me ...

28. cybersniper
    Member
    Posted 3 years ago #
    

    @john.andrews
    thanks for the detailed explanation. Works beautifully! Now I just need to do the css styling ... thanks much!

29. jasongeiger
    Member
    Posted 3 years ago #
    

    Saku4388

    The best way to go about it is to run custom sql queries for what you want to retrieve.

    I would recommend looking through some of the custom queries that have been given above to get familiar with custom sql queries.

    The thing is: grabbing tags and categories is already pretty intense to follow, adding post meta is only going to make it more difficult because you are adding another table to the mix.

    Good Luck!

30. Pacesol
    Member
    Posted 3 years ago #
    

    I typically go with used over new, unless it's something I really can't find.

Topic Closed

This topic has been closed to new replies.