FacebookTwitter

How To Sort Grouped Product’s Children – WooCommerce 2.5.x

When you have hundreds or thousands of products, manually changing menu order value in each product under advanced tab in product edit page is not an acceptable method. Even “Sort Products” section of edit products page is too time consuming. There are a couple ways you can do this much faster.

By on Apr 3, 2016 in Geek | 0 comments

Share On GoogleShare On FacebookShare On Twitter
You MUST backup your database and your web files before you try this method.  If you decide to try this method, please do so at your own risk. I will not be responsible for any site breakage.

The order of child products in a group product page in WooCommerce is based on “menu_order” value in ascending order. You can change each product’s menu order value in “Edit Product” page under “Advanced” tab.

The problem is you can’t go through this process when you have hundreds or thousands or products and finish in timely manner.

There are several ways you can do to re-sort your child products in much faster manner.

Method 1 – overwrite sort rule in functions.php

If the sorting order rule can be defined in a database query, you can use a filter to overwrite the query rule. What you want to do in your theme’s functions.php is to overwrite “woocommerce_grouped_children_args” filter.

Overwrite woocommerce_grouped_children_args in functions.php

/**
 * This example sorts the children products based on the post date, newest at the top
 */
add_filter( 'woocommerce_grouped_children_args', 'fuyuko_custom_grouped_children_args' );
function 'fuyuko_custom_grouped_children_args( $args ){
    $args['orderby'] = 'post_date';
    $args['order'] = 'DESC';
    return $args;
}

Method 2 – overwrite menu_order directly via database

You must be able to access and have the right to modify database directly in order to do this method. If you don’t, you are stuck with Method 1.

So if the child products orders are completely custom (not based on the date, or sort alphabetically based on product name or SKU), this is the way to go.

1. You want to export the relevant products from your database. Be sure to export “ID”, “post_title”, “menu_order”, and “parent_post”.

SELECT ID, post_title, menu_order, parent_post FROM your_database_name.wp_post WHERE post_type LIKE 'product';

2. I normally work with a spreadsheet software and sort the products in order that I want. Then assign a new sort order number. menu_order value doesn’t have to be unique. I can simply assign 1 at the top and use the function to increment by 1 each row.

3. I also generate SQL update statements using a CONCATENATE function in the spreadsheet.

UPDATE your_database_name.wp_post SET menu_order = <new value> WHERE ID = <product id>;

Once all the update statements are generated I simply copy and paste the update statements, and execute directly on the database.

Troubleshoot – Not seeing the change?

One of the common problems you running into with group product’s sorting order update is that you don’t see the updated order even if you make the change. The order stays the old way, and you keep wondering what you did wrong.

Most likely, IT’S NOT YOU.

You can read (posted below) the get_children() function in WooCommerce to see why this is happening.

The get_children() function only queries the database if the information is not yet stored in transient data. And the transient data is stored in the database for 30 days once it’s created.

So if there is a wp_product_children trasient data for a product, you won’t see the change.

There is “Clear Transient” tool in WooCommerce System Status -> Tool. Unfortunately, from my experience, the tool does NOT delete wc_product_children transient data.

SO you’ve got to call a DETELE statement in database:

DELETE FROM your_database_name.wp_options WHERE option_name LIKE '%wp_product_children_%';

Now you can see the updated sort order.

Resource – get_children() in WC_Product_Grouped 2.3.0

/**
 * Return the products children posts.
 *
 * @access public
 * @return array
 */
public function get_children() {
	if ( ! is_array( $this->children ) || empty( $this->children ) ) {
    	$transient_name = 'wc_product_children_' . $this->id;
		$this->children = array_filter( array_map( 'absint', (array) get_transient( $transient_name ) ) );
    	if ( empty( $this->children ) ) {
    		$args = apply_filters( 'woocommerce_grouped_children_args', array(
    			'post_parent' 	=> $this->id,
    			'post_type'		=> 'product',
    			'orderby'		=> 'menu_order',
    			'order'			=> 'ASC',
    			'fields'		=> 'ids',
    			'post_status'	=> 'publish',
    			'numberposts'	=> -1,
    		) );
	        $this->children = get_posts( $args );
			set_transient( $transient_name, $this->children, DAY_IN_SECONDS * 30 );
		}
	}
	return (array) $this->children;
}

Leave a Reply