Author Centric Sub-Category Lists?

  • Resolved SlightlyAmiss

    (@slightlyamiss)


    15 years, 3 months 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!

Viewing 2 replies - 1 through 2 (of 2 total)
  • Thread Starter SlightlyAmiss

    (@slightlyamiss)

    15 years, 3 months 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?

    Thread Starter SlightlyAmiss

    (@slightlyamiss)

    15 years, 3 months 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!

Viewing 2 replies - 1 through 2 (of 2 total)

The topic ‘Author Centric Sub-Category Lists?’ is closed to new replies.