Voor gevorderden! Voor gevorderden!

Royaume des Pays-Bas - Viewer discretion is advised!

Zeker de moeite waard:

Finding untagged posts in WordPress

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!

3 Responses to “Finding untagged posts in WordPress”

  1. LeLLe Says:

    ubernerd voor gevorderden!

  2. - [ UU-DOT ] - Voor gevorderden! » Blog Archive » Twitter Updates for 2011-01-25 Says:

    [...] March 2005 « Finding untagged posts in WordPress [...]

  3. tiki god Says:

    Just found this to be incredibly useful, even 3 years later! I did limit it down to just the ID and post name though and formated the ID so it was an edit link:

    print(‘ID.’&action=edit>’.$post->post_title.’‘);
    print(‘

Leave a Reply