I have finally been able to come up with a solution based on some help from my more MySQL oriented question at Stack Overflow:
add_filter( 'posts_fields', 'posts_fields' );
function posts_fields( $fields ) {
if ( ( is_woocommerce() || is_search() ) && ! is_admin() ) {
$add = "
, CASE wt.slug
WHEN 'antique' THEN 1
WHEN 'reproduction' THEN 2
ELSE 0 END
as slug_order
";
return $fields . $add;
}
return $fields;
}
add_filter( 'posts_join', 'meta_join' );
function meta_join( $join ) {
if ( ( is_woocommerce() || is_search() ) && ! is_admin() ) {
global $wpdb;
$add = '';
$add = "
LEFT JOIN $wpdb->postmeta stock ON (
$wpdb->posts.ID = stock.post_id
AND stock.meta_key = '_stock_status'
)
LEFT JOIN $wpdb->term_relationships wtr ON (
$wpdb->posts.ID = wtr.object_id
AND stock.meta_value="outofstock"
)
LEFT JOIN $wpdb->term_taxonomy wtt ON (
wtr.term_taxonomy_id = wtt.term_taxonomy_id
AND wtt.taxonomy = 'product_tag'
)
LEFT JOIN $wpdb->terms wt ON (
wtt.term_id = wt.term_id
AND wt.slug IN( 'antique','reproduction' )
)
";
return $join . $add;
}
return $join;
}
add_filter('posts_where', 'hide_sold_antiques');
function hide_sold_antiques( $where ) {
if ( ! is_product() && ! is_search() && is_woocommerce() && ! is_admin() ) {
$add = '';
if ( ! show_sold_antiques() ) {
global $wpdb;
$add = " AND NOT (
stock.meta_value="outofstock"
AND $wpdb->posts.ID IN (
SELECT object_id
FROM $wpdb->term_relationships wtr
INNER JOIN $wpdb->term_taxonomy wtt ON (
wtr.term_taxonomy_id = wtt.term_taxonomy_id
AND wtt.taxonomy = 'product_tag'
)
INNER JOIN $wpdb->terms wt ON (
wtt.term_id = wt.term_id
AND wt.slug = 'antique'
)
)
) ";
}
$add .= " AND NOT (
stock.meta_value="outofstock"
AND (
CASE wt.slug
WHEN 'antique' THEN 1
WHEN 'reproduction' THEN 2
ELSE 0 END
) = 0
) ";
return $where . $add;
}
return $where;
}
add_filter( 'posts_groupby', 'product_groupby' );
function product_groupby( $groupby ) {
if ( ( is_woocommerce() || is_search() ) && ! is_admin() ) {
return $groupby . ', slug_order';
} else {
return $groupby;
}
}
add_filter( 'posts_orderby', 'product_meta_orderby' );
function product_meta_orderby( $orderby ) {
if ( ( is_woocommerce() || is_search() ) && ! is_admin() ) {
global $wpdb;
return " $wpdb->posts.post_type DESC, stock.meta_value ASC, slug_order DESC, $wpdb->posts.post_date DESC ";
}
return $orderby;
}
function show_sold_antiques() {
$show_sold_antiques = false;
$showsold_parameter = null;
if ( isset( $_GET[ 'showsold' ] ) ) {
$showsold_parameter = $_GET[ 'showsold' ];
if ( $showsold_parameter === 'true' ) {
$show_sold_antiques = true;
}
}
return $show_sold_antiques;
}