Custom sorting for WooCommerce products order by

I had to really customize the order by of the main WP query. Finally looks like I nailed it.

function pn_woocommerce_product_query( WP_Query $wp_query ) {
if ( $wp_query>get( 'orderby' ) == 'menu_order title' ) {
$wp_query>set( 'meta_query', [
'relation' => 'AND',
'internal_stock' => [
'key' => '_manage_stock',
'compare' => 'EXIST',
],
'stock_status' => [
'key' => '_stock_status',
'compare' => 'EXIST',
],
'price' => [
'key' => '_price',
'compare' => 'EXISTS',
],
] );
$wp_query>set( 'orderby', [
'internal_stock' => 'ASC',
'stock_status' => 'ASC',
'price' => 'ASC',
] );
}
}
add_action( 'woocommerce_product_query', 'pn_woocommerce_product_query' );
function pn_posts_orderby_request( $orderby ) {
// Output based on pn_woocommerce_product_query()
if ( $orderby == 'CAST(pn_postmeta.meta_value AS CHAR) ASC, CAST(mt1.meta_value AS CHAR) ASC, CAST(mt2.meta_value AS CHAR) ASC' ) {
$orderby = "pn_postmeta.meta_value ASC, FIELD (mt1.meta_value, 'instock', '3days', 'outofstock'), mt2.meta_value ASC";
}
return $orderby;
}

view raw
order-by-field.sql
hosted with ❤ by GitHub

SQL to get WooCommerce product categories (terms)

For an import script I needed to get WooCommerce product categories so I ended up looking the generated query in /var/log/mysql/query.log for get_the_terms($postID, ‘taxonomy_name’)

I ended up using the following SQL query to get product categories:

SELECT t.*, tt.*
FROM wp_terms AS t 
INNER JOIN wp_term_taxonomy AS tt ON tt.term_id = t.term_id 
INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id
WHERE tt.taxonomy IN ('product_cat') AND tr.object_id IN (11448)