Setting All Drafts to 'exclude-from-catalog' in WooCommerce

Is there anyway to do this programatically? Meaning via a PHP script on my localhost, exporting and then importing back in– or any other way that would work.

Here’s what I’m talking about:

The table wp_terms is where I found the exclude-from-catalog term_id

enter image description here

I’ve tested one product page out on the backend, and then did this query in MySQL/PHPMyAdmin

SELECT *
FROM wp_posts p
INNER JOIN wp_postmeta pm ON ( pm.post_id = p.ID AND pm.meta_key='_visibility' )
INNER JOIN wp_term_relationships tr ON (p.ID = tr.object_id)
INNER JOIN wp_term_taxonomy tt ON (tr.term_taxonomy_id = tt.term_taxonomy_id)
INNER JOIN wp_terms t ON (t.term_id = tt.term_id)
WHERE p.post_type = 'product'
and t.term_id = 7;

And I can see the product I set this to with a whole bunch of columns.

enter image description here

The post_id / object_id both reference the product page ID I manually set to exclude from catalog.

My issue is, if I do a query like this

SELECT *
FROM wp_posts p
INNER JOIN wp_postmeta pm ON ( pm.post_id = p.ID AND pm.meta_key='_visibility' )
INNER JOIN wp_term_relationships tr ON (p.ID = tr.object_id)
INNER JOIN wp_term_taxonomy tt ON (tr.term_taxonomy_id = tt.term_taxonomy_id)
INNER JOIN wp_terms t ON (t.term_id = tt.term_id)
WHERE p.post_type = 'product'
and post_status = 'draft';

It’s returning all the post meta for every instance on every page… that query almost returns 400,000 results.

Seeing how wp_terms obviously has a close relationship with wp_term_relationships and wp_term_taxonomy I’m thinking this is not going to be a simple task.

Furthermore it seems only a new meta_id along with the taxonomy product_visibility is created only when you specify something in the backend (such as featured, outofstock, or exclude-from-catalog).

Is this an impossible task? Is the WordPress/WooCommerce terms structured so I’m not able to do this and have to manually set a product_visibility for each of my drafted products in wp-admin?

Solutions Collecting From Web of "Setting All Drafts to 'exclude-from-catalog' in WooCommerce"

Look into wp_set_object_terms()

You could do something along the lines of:
wp_set_object_terms( $post_id, 'exclude-from-catalog', 'product_visibility' );

Getting all posts by status, use Wp_Query. I added the 'fields' => 'ids' param to limit the query to only ids, rather than gathering whole post objects.

On an array of posts that have Draft or Pending as a status or are an auto-draft

 function catalog_exclude_drafts() {
 $args = array(
               'post_type'   => 'product',
               'post_status' => array( 'pending', 'draft', 'auto-draft' ),
               'posts_per_page' => -1, 
               'fields'      => 'ids'
               );
            $drafts = new WP_Query( $args );
            $ids    = $drafts->posts;

            $map    = array_map( 'wrapper_for_set_obj', $ids );
 }

function wrapper_for_set_obj( $post_id ) {
    wp_set_object_terms( $post_id, 'exclude-from-catalog', 'product_visibility' );     
}

You could hook the above (or some perfected variant) on init to run once on localhost or something. To future proof your Drafts, hook it on save_post. In fact, you can hook it on save_post_products since WP 3.7 introduced save_post{post_type}.

One time update:
add_action( 'init', 'catalog_exclude_drafts' );

Going forward:
add_action( 'save_post_products', 'wrapper_for_set_obj' );