在WooCommerce中以最高价格获取一系列独特的产品属性/值

Question

In WooCommerce, I would like to:

1. Get the unique values for a certain product attribute (From all my products), assuming all products have that particular attribute set as something.

2. Get the list of unique attribute values with the highest product price.

For Example:

Product 1:
Product-Attribute = Green
Price = $100

Product 2:
Product-Attribute = Red
Price = $50

Product 3:
Product-Attribute = Green
Price = $50

Expected result (an array):

Green : $100
Red : $50

Any ideas on how to code this so that it returns an array?

Answer 1

For Variable products and its variation attributes name and term value name with the highest variation price in an array, you will try the following custom function:

function get_variations_attributes_values_highest_price(){
    global $wpdb;

    // SQL query
    $all_attributes = $wpdb->get_results("SELECT * FROM {$wpdb->prefix}woocommerce_attribute_taxonomies");
    $product_attributes = array();
    foreach( $all_attributes as $value ){
        $product_attributes = 'pa_'.$value->attribute_name;
    }
    //$pa_taxonomies = "'".implode("','", $product_attributes)."'";

    // The 2nd SQL query
    $query = $wpdb->get_results( "
        SELECT p.ID, pm.meta_key as attr, pm.meta_value as term, pm2.meta_value as price
        FROM {$wpdb->prefix}postmeta as pm
        INNER JOIN {$wpdb->prefix}posts as p ON pm.post_id = p.ID
        INNER JOIN {$wpdb->prefix}postmeta as pm2 ON pm.post_id = pm2.post_id
        WHERE p.post_type IN ('product_variation','product')
        AND p.post_status LIKE 'publish'
        AND pm.meta_key LIKE 'attribute_pa_%'
        AND pm2.meta_key LIKE '_price'
        AND pm2.meta_value != ''
        ORDER BY pm.meta_key ASC, pm.meta_value ASC, CAST(replace(pm2.meta_value, 'MDT ', '') AS UNSIGNED) ASC
    " );
    //print_pr($query);

    $ordered_results = $results = array();

    // Loop 1: Filtering and removing duplicate terms keeping highest price
    foreach( $query as $values ){
        if( !empty($values->price)){
        $filter_key = $values->attr .' '.$values->term;
            $ordered_results[$filter_key] = (object) array( 'attr' => $values->attr,
                'term' => $values->term, 'price' => $values->price );
        }
    }

    // Loop 2: Get the real name values, formatting data
    foreach( $ordered_results as $result ){
        $taxonomy = str_replace('attribute_' , '', $result->attr );
        $attr_name = get_taxonomy( $taxonomy )->labels->singular_name; // Attribute name
        $value_name = get_term_by( 'slug', $result->term, $taxonomy )->name; // Attribute value term name
        $results[$attr_name.' - '.$value_name] = wc_price($result->price);
    }
    return $results; 
}

Code goes in function.php file of the active child theme (or active theme).

Tested and works.

---

## --- USAGE --- ##

// RAW ARRAY OUTPUT
echo '<pre>'; print_r(get_variations_attributes_values_highest_price()); echo '</pre>';

You can make little changes easily to match your needs (the honey for TheBear)…