php - 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

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.

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?

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

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.

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?

• php
• woocommerce-offtopic
• mysql

Share Improve this question asked Jul 8, 2017 at 5:17 bbrumanbbruman 48411 gold badge55 silver badges1717 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 2

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' );