1

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?

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

  • Note: there may be other post_stati to consider, but if only drafts, you can just set 'post_status' => 'draft' – hwl Jul 8 '17 at 7:32
  • Ah, thank you :) Brilliant response. I should've known WordPress already had their own clever way of doing these things. – bbruman Jul 8 '17 at 9:25
  • But I'm having issues with it... does not seem to be working. It definitely fired on init. I also tried it by saving a draft with keeping the second function active. All the drafts still say Catalog visibility: Shop and search results instead of Search Results Only. Additionally that MySQL t.term_id = 7 is outputting the same single result. Not really sure what the issue is. I'll take a closer look at it tomorrow definitely. – bbruman Jul 8 '17 at 9:36
  • Worked great! Thanks again. Just needs a couple minor edits. 'post_type' => 'product', instead of products. I also had to add to the array 'posts_per_page' => -1 because it wasn't retrieving all the posts. Now all drafts are Search Results Only... so glad I didn't have to do this one-by-one, there are thousands :) – bbruman Jul 8 '17 at 12:12
  • hey! awesome. yeah, i should have figured the 'posts_per_page' => -1, I'll update the answer for anybody else running across it with that and correct post_type. Side note: The developer reference is really helpful for finding functions, classes, and various hooks as you need them. – hwl Jul 8 '17 at 15:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.