I’ve been looking for a decent way to get a list of all posts in wordpress that have no tags yet. I couldn’t find a plugin and on forums the recommended way seemed to be to do a wp_query and loop over the results.
I figured that this was not a very efficient way to do this, especially not when a lot of posts were involved. Eventually I rolled the following sql to do the trick.

SELECT * FROM wp_posts p LEFT JOIN wp_users u ON (p.post_author = u.ID) WHERE p.post_status = 'publish' AND p.post_type='post' AND p.ID NOT IN (SELECT object_id FROM wp_term_relationships tr LEFT JOIN wp_term_taxonomy tt ON (tr.term_taxonomy_id = tt.term_taxonomy_id) WHERE taxonomy='post_tag')

If you have a large site with many authors, this will help you to build a naming and shaming list:

SELECT display_name, COUNT(display_name) as aantal FROM wp_posts p LEFT JOIN wp_users u ON (p.post_author = u.ID) WHERE p.post_status = 'publish' AND p.post_type='post' AND p.ID NOT IN (SELECT object_id FROM wp_term_relationships tr LEFT JOIN wp_term_taxonomy tt ON (tr.term_taxonomy_id = tt.term_taxonomy_id) WHERE taxonomy='post_tag') GROUP BY display_name ORDER BY aantal DESC

Enjoy!

This entry was posted on Monday, January 24th, 2011 at 20:09 and is filed under Voor Gevorderden.