WordPress.org

1. SlightlyAmiss
   Member
   Posted 1 year ago #

   Hello,

   I am interested in displaying a list of categories that an author has posted in. Something in this support thread (specifically the 15th post) helped me to accomplish showing a full list of categories an author has posted in.

   Here's a quick reference to the code I currently have on my author.php template:

   <?php
   $categories = $wpdb->get_results("
   	SELECT DISTINCT(terms.term_id) as ID, terms.name, terms.slug
   	FROM $wpdb->posts as posts
   	LEFT JOIN $wpdb->term_relationships as relationships ON posts.ID = relationships.object_ID
   	LEFT JOIN $wpdb->term_taxonomy as tax ON relationships.term_taxonomy_id = tax.term_taxonomy_id
   	LEFT JOIN $wpdb->terms as terms ON tax.term_id = terms.term_id
   	WHERE 1=1 AND (
   		posts.post_status = 'publish' AND
   		posts.post_author = '4' AND
   		tax.taxonomy = 'category' )
   	ORDER BY terms.name ASC
   ");
   ?>
   <ul>
   	<?php foreach($categories as $category) : ?>
   	<li>
   		<a href="<?php echo get_category_link( $category->ID ); ?>" title="<?php echo $category->name ?>"><?php echo $category->name ?></a>
   	</li>
   	<?php endforeach; ?>
   </ul>

   Is it possible to include a further condition in the custom database calls to only show child categories of a specified parent category ID? Or maybe it's easier based on the name of the parent category?

   How to write custom calls to the database just escapes me. I've done extensive searching of this site and on Google to try and see if someone asked a similar question. Sadly, I didn't find anything.

   If anyone could point me in the right direction I would be most grateful! ^_^

   Thanks!

2. SlightlyAmiss
   Member
   Posted 1 year ago #

   Here's an additional piece of code that I came across which correctly outputs a list of categories with a specific parent ID.

   $parent = 29;// your category parent ID
   $where = "AND tt.parent = '$parent'";
   $in_taxonomies = "'category'";
   $orderby = 't.term_id';
   $order = 'ASC';
   $limit = 'LIMIT 0,100';
   
   $query = "SELECT t.name FROM $wpdb->terms AS t INNER JOIN $wpdb->term_taxonomy AS tt ON t.term_id = tt.term_id WHERE tt.taxonomy IN ($in_taxonomies) $where ORDER BY $orderby $order $limit";

   However, it's not author-centric. Is there a way to combine some of the rules of the first and second queries I've found to achieve something author centric?

3. SlightlyAmiss
   Member
   Posted 1 year ago #

   I figured out how to modify the first block of code in my original post to output only sub-categories of a specified ID. I'll post it here in case anyone needs reference.

   <?php
   $categories = $wpdb->get_results("
   	SELECT DISTINCT(terms.term_id) as ID, terms.name, terms.slug
   	FROM $wpdb->posts as posts
   	LEFT JOIN $wpdb->term_relationships as relationships ON posts.ID = relationships.object_ID
   	LEFT JOIN $wpdb->term_taxonomy as tax ON relationships.term_taxonomy_id = tax.term_taxonomy_id
   	LEFT JOIN $wpdb->terms as terms ON tax.term_id = terms.term_id
   	WHERE 1=1 AND (
   		posts.post_status = 'publish' AND
   		posts.post_author = '4' AND
   		tax.taxonomy = 'category' AND
                   tax.parent = '29'
                   )
   	ORDER BY terms.name ASC
   ");
   ?>
   <ul>
   	<?php foreach($categories as $category) : ?>
   	<li>
   		<a href="<?php echo get_category_link( $category->ID ); ?>" title="<?php echo $category->name ?>"><?php echo $category->name ?></a>
   	</li>
   	<?php endforeach; ?>
   </ul>

   Change the tax.parent value to the parent category's ID. In my case, the list of sub-categories all needed to spawn from parent category 29.

   Hope this helps!

Topic Closed

This topic has been closed to new replies.