[Escalated to 2nd Tier] Cannot sort product category archive by numeric Types custom field

[Author]

Posts

[scottL-3]

I am trying to:
Order the product category archive by a sequence number field we added to products with Types

Link to a page where the issue can be seen:
hidden link
(go to second page of results -- the Ashland Modern Ledge product has a value of 99 in its sequence number field and the Cambridge Blend products at the end of the page have the value of 30. The archive has primary sort set to the sequence number field and secondary sort set to post title ascending.

I expected to see:
Ashland Modern Ledge near the end of the page and Cambridge products earlier in the results.
All products have a value in the sequence number field but not necessarily a unique number.

Instead, I got:
The order you'll see if you visit the page which is not correct.

I am not sure what is causing this .. I tried setting both the Toolset Views product category archive and WooComerce to sort on menu order with no better result. The site does use relevanssi and I saw a ticket that said relevanssi affects archive search order with Toolset but that post wasn't specific to WooCommerce Blocks/View and seemed old.

I will rebuild our staging site in case you need access and so I have a safe place to try monitoring queries and turning off various plugins to try to find the problem but if this is already a well known issue to you and you can save me some time, please reply.

[scottL-3]

Quick update:

I updated our staging site with the current live site so I could test more.

I disabled as many plugins as I could practically disable including all WooCommerce add-ons.

I switched to the twenty twentyone theme.

Still having the same issue.

[scottL-3]

A little more info. I ran Views Debug and the query is definitely not sorting by the settings in the Toolset Archive nor is it sorting by the WooCommerce settings (custom + title). Not sure why but here's the debug output which is trying to sort by post date:

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) LEFT JOIN wp_term_relationships AS tt1 ON (wp_posts.ID = tt1.object_id) INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) INNER JOIN wp_postmeta AS mt1 ON ( wp_posts.ID = mt1.post_id ) LEFT JOIN wp_posts AS p2 ON (wp_posts.post_parent = p2.ID) WHERE 1=1 AND (
wp_term_relationships.term_taxonomy_id IN (2704)
AND
tt1.term_taxonomy_id IN (2704)
) AND (
wp_postmeta.meta_key = 'wpcf-sequence-number'
AND
(
( mt1.meta_key = 'wpcf-unit-of-measure' AND mt1.meta_value != '' )
)
) AND wp_posts.post_type IN ('post', 'product', 'video', 'product-document') AND (((wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') OR (wp_posts.post_status = 'inherit' AND (p2.post_status = 'publish' OR p2.post_status = 'private')))) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date ASC LIMIT 48, 48

[Luo Yang]

Hello,

I don't see similar report, and the problem you mentioned above is abnormal, please check these:
1) In case it is a compatibility problem, please deactivate all other plugins, and switch to WordPress default theme 2021, deactivate all custom PHP/JS code snippets, and test again

2) If the problem still persists, please provide database dump file(ZIP file) of your website, you can put the package files in your own google drive disk, share the link only, I need to test and debug it in my localhost, thanks
https://toolset.com/faq/provide-supporters-copy-site/

[scottL-3]

I will need to clean out customers and orders before I can give you a copy of the database so that will probably not happen until tonight or tomorrow.

As I already said in prior replies, I already turned off all plugins that can be turned off and switched to a default theme.

One thing to note is that the product category taxonomy is used for more than one post type and only one of those post types has the sequence number field we want to sort on. But the archive is filtered to only find posts of this type because they are the only ones with a unit of measure field (which has a default value so is never blank). Not sure if this is relevant or not but thought it worth mentioning.

[Luo Yang]

I can not duplicate the same problem in my localhost, please update here when the database dump file is ready, private message box enabled again

[Luo Yang]

Thanks for the details, I am downloading the files, will update here if find anything

[Luo Yang]

I got some Mysql errors when import the SQL file you provided above, but I can login into your staging website, and trying to export the database only duplicator package file from it, will update here if find anything

[Luo Yang]

Thanks for the details, I can see the problem in with your website database dump file.

The Toolset WordPress Archive is using "pre_get_posts" action hook to change query, but in Woocommerce archive page, this action hook does not work, I will escalate this issue.

Currently, please try with filter hook, for example, add below codes into your theme file "functions.php":

add_filter('woocommerce_get_catalog_ordering_args', 'sort_book_archive_loop');
function sort_book_archive_loop($arg) {
	if( !is_admin() && is_product_category() ){
		$args = array(
			'orderby'  => array('meta_value_num' => 'ASC', 'title' => 'ASC'),
			'meta_key' => 'wpcf-sequence-number',
		);
    }
	return $args;
}

[scottL-3]

The workaround code works, thanks.

Not marking this resolved as it would be much better if I didn't need custom code to make this work (even on this site there are several views and archives where it would be useful to be able to orderby different things). Hoping the developers can find and fix whatever is preventing the normal settings from working with WooCommerce archives.

[Luo Yang]

Thanks for the confirmation, I have escalated this issue, will update here if there is anything news.