• Resolved OsakaWebbie

    (@osakawebbie)


    Having added two custom taxonomies to a site, I’m trying to update all the posts to assign terms. The admin list of posts has a column for each of the taxonomies (both core and custom), but I can’t sort on those columns. How can I find all posts that don’t have any term for a given taxonomy? (Don’t worry about the fact that some are custom – if you know how to do it for tags or categories, I’m sure the method will be the same for my others.)

    If the admin interface doesn’t have a way and there is no simple plugin to sort or filter this way, I’m okay with doing a raw database query – I do know SQL. But I’m struggling to understand the relationship of the relevant tables, which seem to be posts, term_taxonomy, terms, term_relationships, and perhaps others.

    • This topic was modified 2 years, 7 months ago by Jan Dembowski. Reason: Moved to Fixing WordPress, this is not an Everything else WordPress topic
Viewing 11 replies - 1 through 11 (of 11 total)
  • Moderator bcworkz

    (@bcworkz)

    If you know PHP, the easiest is to use WP_Query class and its “tax_query” arg. Use its 'operator' => 'NOT_EXISTS', arg to get posts without an assigned term for the specified taxonomy.
    https://developer.wordpress.org/reference/classes/wp_query/

    I suspect PHP isn’t your thing, but since you know SQL, here’s the SQL that WP builds to query for posts with no “genre” taxonomy term. You should be able to adapt it to your needs.

    SELECT   wp_posts.* FROM wp_posts  WHERE 1=1  AND ( 
      NOT EXISTS (
    				SELECT 1
    				FROM wp_term_relationships
    				INNER JOIN wp_term_taxonomy
    				ON wp_term_taxonomy.term_taxonomy_id = wp_term_relationships.term_taxonomy_id
    				WHERE wp_term_taxonomy.taxonomy = 'genre'
    				AND wp_term_relationships.object_id = wp_posts.ID
    			)
    ) AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'future' OR wp_posts.post_status = 'draft' OR wp_posts.post_status = 'pending' OR wp_posts.post_status = 'private') GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC "

    Obviously there’s some cruft that isn’t needed, such as 1=1 AND. It’s there because of the way WP builds queries. There always has to be a WHERE clause even if one is not needed. Then WP ANDs in other clauses as required.

    Thread Starter OsakaWebbie

    (@osakawebbie)

    Thanks. It’s not that I don’t know PHP, but building something with WP_Query requires some sort of infrastructure (a plugin or a theme template or some other way to run it and display the results), which is not my strength and likely a lot of work just for an occasional check. The infrastructure would take me a lot longer to figure out than the query itself.

    After posting my question, I kept exploring the database structure and eventually wrote this query, which is basically the same sort of approach as yours but in manual SQL style instead of WP style:

    SELECT ID,post_title FROM wpstg0_posts p WHERE post_type='post' AND post_status='publish' AND ID NOT IN (SELECT object_id FROM wpstg0_term_relationships tr INNER JOIN wpstg0_term_taxonomy tt ON tr.term_taxonomy_id=tt.term_taxonomy_id AND tt.taxonomy='issue')

    Of course I’ll have to then search within WP for each of the results using its title, but that would probably be faster (for me anyway) than building a way to call WP_Query – ideally a filter of the Posts list on the admin side, so I can use Quick Edit. But if that’s easier than I’m imagining, I’m all ears.

    Moderator bcworkz

    (@bcworkz)

    Now that you’re clearer on how the taxonomy tables inter-relate, you could do UPDATE queries to add terms in batches. But there’d need to be some way in code to distinguish which posts get assigned which terms.

    You’re right, building the UI to implement some coding concept can be much more time consuming than the concept itself. For a utilitarian UI where it doesn’t matter what it looks like nor does it need to be very user friendly, you can place all relevant code on a custom page template, then add a page that utilizes that template. Requesting the page then executes the template code. Don’t let the need to build a UI deter you from using PHP. It needn’t be that difficult 🙂

    I probably needn’t mention it, but I’ll do so anyway: Make backups of any tables involved before running any UPDATE query!

    Thread Starter OsakaWebbie

    (@osakawebbie)

    In my case, each post with no terms needs human research and decisions, so batch UPDATE is not applicable.

    Even the page template that you call utilitarian would be new to me, so first I’d have to study how to make one and then write whatever code needs to surround the WP_Query, and I wouldn’t know how extensive that would be until I start doing it. Apparently you already know that a page template would be easier to write than a little plugin to add a filter to the Posts list on the admin side (or to allow sorting the Posts list by taxonomy columns, which I’m surprised WP doesn’t already do and that no one has already written a plugin to add it) – I would have no idea which is easier, since both are new territory for me. A plugin for the admin side has three advantages: (1) it’s exactly where it would be most handy, allowing Quick Edit, (2) it would be theme-independent, so it could be used on other sites, (3) it’s not on the front end where it might get indexed by SE bots. Do you have a feel for how hard it might be to write such a plugin? I definitely speak PHP, but mostly outside of WP, so when I write code in WP I spend 95% of my time looking up structure and functions and such, not writing.

    • This reply was modified 2 years, 7 months ago by OsakaWebbie.
    Moderator bcworkz

    (@bcworkz)

    You can use any of the theme’s existing templates as a starting point. page.php or index.php make the most sense. Your template needn’t be much more than a static HTML page though; but for one required WP line: do_action('wp_head'); at the end of the <head> section. The rest is whatever PHP you want.

    Creating the the plugin “framework” is way simpler than it sounds. Just certain comment lines in a file.
    https://developer.wordpress.org/plugins/plugin-basics/

    As you say, the pain is in developing a UI. The admin area alternative to a page template would be a plugin settings page added via add_menu_page().
    https://developer.wordpress.org/reference/functions/add_menu_page/

    The callback you pass as $function is responsible for most of the admin page’s content and function. WP adds in its menu and admin bar before calling your callback. There’s a Settings API to help you achieve the standard admin area look and layout, but it’s not required.

    A large part of plugin development involves filter and action hooks. Until you understand how these work things will be rather confusing. They really aren’t that complicated, but their functioning isn’t immediately clear to many.
    https://developer.wordpress.org/plugins/hooks/

    The other main challenge with plugin development is determining what hooks you should use. Searching the the web can yield answers. Other times you’d need to sift through WP source code, so some familiarity with its file and code organization is helpful, but this can only come with experience. TBH it’s a tangled mess. It’s what happens with code maintained over decades by many hundreds of contributors.

    While listing posts on a page template or admin page would be easiest to do, I think it’ll be worth it to instead alter the usual list table query to list only posts with no terms assigned. This can be done through the “pre_get_posts” action by defining the array assigned to the “tax_query” query var. The trick here is to only alter the correct list table query, as all queries pass through this action. Checking is_admin() and “post_type” query var might be good enough. Comment out the add_action() call to regain normal list table views.

    With a list table of termless posts, the quick edit feature can be used to assign terms. No need to build a UI then, just change the existing one.

    Thread Starter OsakaWebbie

    (@osakawebbie)

    Thanks – you gave me a lot of clues.

    FYI: Plugin framework is not my worry – I already have a site-specific plugin where I moved some stuff out of functions.php that isn’t theme-dependent. So I can add whatever I want there.

    The other main challenge with plugin development is determining what hooks you should use. … TBH it’s a tangled mess.

    Indeed.

    I found someone who claimed on stackexchange to have made a taxonomy column sortable, so I tried his code (even though his answer only got one up-vote, so I didn’t hold my breath). It does make the column header clickable, and the posts with a term do appear to sort, but the posts with no terms don’t end up together. So that might be handy sometime, but it doesn’t fulfill my current wish.

    So I embarked on the journey to make an action for pre_get_posts. After a lot of research about parameters and syntax, this is what I came up with, but so far it doesn’t work – no posts are returned at all.

    function posts_with_empty_tax($query) {
        //return;  // comment this out to use this function
        $tax_to_check = 'issue';  // set this to desired taxonomy
        if (!is_admin() ) return;
        $all_terms = get_terms(['taxonomy'=>$tax_to_check, 'fields'=>'ids']);
        $tax_query = array(array(
            'taxonomy' => $tax_to_check,
            'field' => 'term_id',
            'terms' => $all_terms,
            'operator' => 'NOT IN',
        ));
        $query->set( 'tax_query', $tax_query );	
    }
    add_action( 'pre_get_posts', 'posts_with_empty_tax' );

    Can you see an obvious mistake somewhere?

    Moderator bcworkz

    (@bcworkz)

    Good effort! The only problem is the tax query you’ve made still requires some imaginary tax term ID to exist despite excluding all possible IDs. Try this variant:

    if (!is_admin() ) return;
        $tax_query = array(array(
            'taxonomy' => $tax_to_check,
            'operator' => 'NOT EXISTS',
        ));

    The remainder of your code outside of this looks fine, but be aware this constraint will be applied to any posts list table. Other post type tables will also only list termless posts or no posts should they not have the “issue” taxonomy associated with the post type. You can get the current post type with $query->get('post_type'). The return value could in theory be either a singleton or an array, but for list table queries I’m pretty sure it’ll always be a singleton string. You might want to also check for this value before applying “tax_query” constraint.

    Thread Starter OsakaWebbie

    (@osakawebbie)

    Wow, I didn’t know about that operator – that works perfectly! And no need to get a collection of all the terms in order to exclude them in the query.

    I added checks to limit it to both a specified post type and a specified (presumably current) user. The chance of anyone else working on the site at the same time as me is near zero (I do 99% of the work), but it’s still good practice. Here’s my final

    function posts_with_empty_tax($query) {
    	/*** SET THESE THINGS BEFORE USING ***/
        //return;  // comment this out to use the function
        $posttype_to_check = 'post';  // set to desired post type: post or guideline
        $tax_to_check = 'issue';  // set to desired taxonomy: issue, article_author, guideline_topic (maybe tag???)
    	$user_checking = 'redacted'; // set to yourself
    	
        if (!is_admin()) return;
    	if ($query->get('post_type') != $posttype_to_check) return;
    	$user = wp_get_current_user();
        if ($user->user_login != $user_checking) return;
        $tax_query = array(array(
            'taxonomy' => $tax_to_check,
            'operator' => 'NOT EXISTS',
        ));
        $query->set( 'tax_query', $tax_query );	
    }
    add_action( 'pre_get_posts', 'posts_with_empty_tax' );

    I have one more question: Changing $tax_to_check to other custom taxonomies works, but I can’t get tag to work – I get no posts even though I know there are lots of posts with no tags. (I suspect category would act the same way if I had any posts without one, but it’s a required field in my setup so I can’t test.)

    Moderator bcworkz

    (@bcworkz)

    Ah yes, you’ve encountered one of those WP quirks that’s difficult to know about except through some frustrating process of discovery. Tags have a slightly unexpected taxonomy name slug: 'post_tag'
    Use that for $tax_to_check and you’ll get tagless posts.

    Expect to encounter more quirks if you find yourself further customizing how WP works for you. Like how featured images are called post thumbnails in code (but are frequently not thumbnail sized). Or the thumbnail image size is actually “post-thumbnail” but large size is just “large”. Note the hyphen instead of the underscore usually used for names.

    Thread Starter OsakaWebbie

    (@osakawebbie)

    Haha, “tagless posts” sounds like tagless T-shirts (or perhaps underwear, since this nomenclature seems to be underWARE). Anyway, thanks for the clue. I suspected the name might be different, but I didn’t know how to check other than what tag archives use in the URL, and my search attempts failed to reveal anything other than “tag”. (Now that I actually search on “post_tag”, of course all kinds of references show up. Hindsight is 20/20.)

    Yeah, the underscore vs. hyphen thing has been driving me nuts for years, especially when writing CSS to target classes. I chalked it up to differences between theme/plugin developers and WordPress, but apparently there’s a variety inside WP core as well. As you said, WP has been developed gradually over many years by thousands of people, and trying to maintain backwards compatibility would make it challenging to clean up the inconsistencies.

    Thanks for all your help!

    Moderator bcworkz

    (@bcworkz)

    You’re welcome! “Tagless underware” — LOL, you funny person 🙂

Viewing 11 replies - 1 through 11 (of 11 total)
  • The topic ‘How to find posts with no terms for a taxonomy’ is closed to new replies.