WordPress › Support Topic: Get Tags specific to Category

WordPress › Support Topic: Get Tags specific to Category http://wordpress.org/support/ WordPress › Support Topic: Get Tags specific to Category en Wed, 25 Nov 2009 21:56:41 +0000 alukeonlife on "Get Tags specific to Category" http://wordpress.org/support/topic/276635#post-1289394 Sat, 21 Nov 2009 19:26:14 +0000 alukeonlife 1289394@http://wordpress.org/support/ 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. MarjWyatt on "Get Tags specific to Category" http://wordpress.org/support/topic/276635#post-1244651 Tue, 13 Oct 2009 05:33:28 +0000 MarjWyatt 1244651@http://wordpress.org/support/ 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: <pre><code>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;</code></pre> 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. whipd09 on "Get Tags specific to Category" http://wordpress.org/support/topic/276635#post-1243361 Mon, 12 Oct 2009 02:30:14 +0000 whipd09 1243361@http://wordpress.org/support/ 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? t31os_ on "Get Tags specific to Category" http://wordpress.org/support/topic/276635#post-1240958 Fri, 09 Oct 2009 16:36:09 +0000 t31os_ 1240958@http://wordpress.org/support/ 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... <pre><code>$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 ) ");</code></pre> kelseydamas on "Get Tags specific to Category" http://wordpress.org/support/topic/276635#post-1240420 Fri, 09 Oct 2009 06:47:43 +0000 kelseydamas 1240420@http://wordpress.org/support/ 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. <pre><code>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</code></pre> MichaelH on "Get Tags specific to Category" http://wordpress.org/support/topic/276635#post-1238549 Wed, 07 Oct 2009 18:10:06 +0000 MichaelH 1238549@http://wordpress.org/support/ Just adding a link to this plugin as it can handle tag/category 'intersections': <a href="http://wordpress.org/extend/plugins/tdo-tag-fixes/" rel="nofollow">http://wordpress.org/extend/plugins/tdo-tag-fixes/</a> john.andrews on "Get Tags specific to Category" http://wordpress.org/support/topic/276635#post-1238530 Wed, 07 Oct 2009 17:56:44 +0000 john.andrews 1238530@http://wordpress.org/support/ 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: <pre><code>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; }</code></pre> In your theme document call the function as follows. Notice it accepts multiple category id's: <pre><code>$args = array( 'categories' => '12,13,14' ); $tags = get_category_tags($args);</code></pre> This will return an array that you could do the following with: <pre><code>$content .= "<ul>"; foreach ($tags as $tag) { $content .= "<li><a href=\"$tag->tag_link\">$tag->tag_name</a></li>"; } $content .= "</ul>"; echo $content;</code></pre> toplink on "Get Tags specific to Category" http://wordpress.org/support/topic/276635#post-1205056 Wed, 09 Sep 2009 12:31:45 +0000 toplink 1205056@http://wordpress.org/support/ excellent work kelseydamas thank you for it t31os_ on "Get Tags specific to Category" http://wordpress.org/support/topic/276635#post-1204691 Wed, 09 Sep 2009 01:46:42 +0000 t31os_ 1204691@http://wordpress.org/support/ Perfect example, thank you kelsey... Clearly someone who knows their joins.. ;) Worked flawlessly first time.. kelseydamas on "Get Tags specific to Category" http://wordpress.org/support/topic/276635#post-1204630 Wed, 09 Sep 2009 00:07:02 +0000 kelseydamas 1204630@http://wordpress.org/support/ 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 <pre><code>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</code></pre> t31os_ on "Get Tags specific to Category" http://wordpress.org/support/topic/276635#post-1192678 Sat, 29 Aug 2009 22:10:24 +0000 t31os_ 1192678@http://wordpress.org/support/ 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). <pre><code>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</code></pre> 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.. <pre><code>Query took 0.0065 sec</code></pre> 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 ** t31os_ on "Get Tags specific to Category" http://wordpress.org/support/topic/276635#post-1191841 Fri, 28 Aug 2009 23:53:48 +0000 t31os_ 1191841@http://wordpress.org/support/ 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.. jasongeiger on "Get Tags specific to Category" http://wordpress.org/support/topic/276635#post-1191709 Fri, 28 Aug 2009 21:13:51 +0000 jasongeiger 1191709@http://wordpress.org/support/ 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. jasongeiger on "Get Tags specific to Category" http://wordpress.org/support/topic/276635#post-1191706 Fri, 28 Aug 2009 21:11:32 +0000 jasongeiger 1191706@http://wordpress.org/support/ This what I've come up with. I think apljdi, is right about custom query. This still seems a bit excessive. <pre><code><?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.'"'; ?> <a href="#<?php echo $el->slug; ?>" id="taglink-tag-<?php echo $el->slug; ?>" rel="tag-<?php echo $el->slug; ?>"><?php echo $el->name; ?></a> // <?php endforeach; ?></code></pre> t31os_ on "Get Tags specific to Category" http://wordpress.org/support/topic/276635#post-1188630 Wed, 26 Aug 2009 14:01:51 +0000 t31os_ 1188630@http://wordpress.org/support/ 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). t31os_ on "Get Tags specific to Category" http://wordpress.org/support/topic/276635#post-1188556 Wed, 26 Aug 2009 12:38:27 +0000 t31os_ 1188556@http://wordpress.org/support/ 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. <ol> <li>Grab IDs for all posts in particular category</li> <li>Grab post tags for each post</li> <li>Remove duplicates</li> <li>Display data</li> </ol> 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.. apljdi on "Get Tags specific to Category" http://wordpress.org/support/topic/276635#post-1179726 Tue, 18 Aug 2009 21:53:57 +0000 apljdi 1179726@http://wordpress.org/support/ 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... Minkowski on "Get Tags specific to Category" http://wordpress.org/support/topic/276635#post-1179690 Tue, 18 Aug 2009 21:33:38 +0000 Minkowski 1179690@http://wordpress.org/support/ I too would like to find a more elegant solution to this. jasongeiger on "Get Tags specific to Category" http://wordpress.org/support/topic/276635#post-1097810 Wed, 10 Jun 2009 23:57:03 +0000 jasongeiger 1097810@http://wordpress.org/support/ Electric Studio, that's not exactly what I was looking for but it was good stepping stone. <pre><code><?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 ?></code></pre> 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. Electric Studio on "Get Tags specific to Category" http://wordpress.org/support/topic/276635#post-1096481 Tue, 09 Jun 2009 19:33:00 +0000 Electric Studio 1096481@http://wordpress.org/support/ OK, this is what i have done, much more basic than i thought: <pre><code><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); ?> <?php endif; ?> <?php if(get_post_meta($post->ID, 'Event Location', true)): ?><?php echo get_post_meta($post->ID, 'Event Location', true); ?> <?php endif; ?> <?php if(get_post_meta($post->ID, 'Event Date Day', true)): ?><?php echo get_post_meta($post->ID, 'Event Date Day', true); ?> <?php endif; ?> <?php if(get_post_meta($post->ID, 'Event Date Month', true)): ?><?php echo get_post_meta($post->ID, 'Event Date Month', true); ?> <?php endif; ?> <?php if(get_post_meta($post->ID, 'Event Date Year', true)): ?><?php echo get_post_meta($post->ID, 'Event Date Year', true); ?> <?php endif; ?> <?php if(get_post_meta($post->ID, 'Event Time', true)): ?><?php echo get_post_meta($post->ID, 'Event Time', true); ?> <?php endif; ?> </li> <?php endforeach; ?> </ul></code></pre> Hopefully this will help someone. Electric Studio on "Get Tags specific to Category" http://wordpress.org/support/topic/276635#post-1096413 Tue, 09 Jun 2009 18:19:48 +0000 Electric Studio 1096413@http://wordpress.org/support/ 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? jasongeiger on "Get Tags specific to Category" http://wordpress.org/support/topic/276635#post-1092278 Fri, 05 Jun 2009 01:10:49 +0000 jasongeiger 1092278@http://wordpress.org/support/ 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.