Filter on a custom product attribute in WooCommerce using meta_query or tax_query [closed]

I’m trying to filter on a “custom product attribute” using either a meta_query, a tax_query, or whatever method works. I can find the attribute using the WC_Product class, but haven’t figured out how to filter it. In the past I have successfully used both meta_query and tax_query on other fields, but anything I try with this custom attribute returns no results.

First, I manually entered a custom product attribute through the WooCommerce section in the WordPress Admin:

Products… Edit product… Attributes… Custom product attribute

Assume the post ID is 123, the custom product attribute’s name is “pa_myProductID” and the value is “123456”

The goal of this example is to only return products where pa_myProductID = “123456”

Here’s what I’ve tried without success:

add_action( 'woocommerce_product_query', 'show_products_by_myProductID' );
function show_products_by_myProductID( $q ) {
    $myProductIdToFind = array('123456');

First I tried a meta_query…

    $meta_query = $q->get( 'meta_query' );

    $meta_query[] = array(
        'key'     => 'pa_myproductid', // also tried 'pa_myProductID'
        'compare'     => '=',
        'value'   => $myProductIdToFind,
    );

    $q->set( 'meta_query', $meta_query );

…but there are no results

Then I tried a tax_query…

    $tax_query = $q->get( 'tax_query' );

    $taxonomy = 'pa_myproductid'; // also tried 'pa_myProductID'
    $tax_query[] = array(
        'taxonomy'  => $taxonomy,
        'field'     => 'slug', // also tried 'name' and 'term_id' and 'term_taxonomy_id'
        'operator'  => 'IN',
        'terms'     => $myProductIdToFind,
    );

    $q->set( 'tax_query', $tax_query );

…also with no results

I can find the attribute using the WC_Product class.

var_dump( wc_get_product('123') );

Here is some of the result:

object(WC_Product_Simple)#2617 (12) { 
["data":protected]=> array(50) { 
...
    ["attributes"]=> array(1) { 
        ["pa_myproductid"]=> object(WC_Product_Attribute)#2666 (1) { 
            ["data":protected]=> array(6) { 
                ["id"]=> int(0) 
                ["name"]=> string(11) "pa_myProductID" 
                ["options"]=> array(1) { 
                    [0]=> string(6) "123456" 
                } 
                ["position"]=> int(0) 
                ["visible"]=> bool(true) 
                ["variation"]=> bool(false) 
            } 
        } 
    } 
    ["default_attributes"]=> array(0) { } 
    ["menu_order"]=> int(5) 
...
}

I can display the attribute like this…

$productGot = wc_get_product('123');
 echo "Name: ";
 var_dump($productGot->attributes['pa_myproductid']['name']);
 echo " -- Value: ";
 var_dump($productGot->attributes['pa_myproductid']['value']);

output of above is…

Name: string(14) "pa_myProductID" -- Value: string(6) "123456"

Can someone help with the syntax or maybe another approach? Any assistance is greatly appreciated! Thanks!

UPDATE (Dec 7, 2019):

I’ve made some progress and found a solution that works for now, with some reservations. See my answer below.

I found a solution that works for now, with some reservations.

First, the field is available via meta_query, although previously I couldn’t find it because the object name is different than the database name. In the WC_Product class it is: [“attributes”]=>array(1)… However I found that the database field name is “product_attributes” and I can use “_product_attributes” as the key in a meta_query.

However, the next challenge is that the product_attributes database field contains serialized data, like this:

a:1:{s:14:"pa_myproductid";a:6:{s:4:"name";s:14:"pa_myProductID";s:5:"value";s:6:"123456";s:8:"position";i:0;s:10:"is_visible";i:1;s:12:"is_variation";i:0;s:11:"is_taxonomy";i:0;}}

So, this works…

$myProductIdToFind = array('a:1:{s:14:"pa_myproductid";a:6:{s:4:"name";s:14:"pa_myProductID";s:5:"value";s:6:"123456";s:8:"position";i:0;s:10:"is_visible";i:1;s:12:"is_variation";i:0;s:11:"is_taxonomy";i:0;}}');

$meta_query[] = array(
    array(
        'key'     => '_product_attributes',
        'value'   => $myProductIdToFind,
        'compare'     => 'IN',
        )
    );

Obviously, the value field is too complicated to use in production, so this isn’t a viable solution.

Next, I tried looping like this…

$myProductIdToFind = array('123456', '654321');

if (count($myProductIdToFind) > 1) {
    $meta_query[0] = array('relation' => 'OR');
}
foreach ($myProductIdToFind as $key => $metaValue) {
    $meta_query[0][] = array(
        'key'     => '_product_attributes',
        'value'   => $metaValue,
        'compare' => 'LIKE',
    );
} 

This works, although my reservation is regarding performance using ‘LIKE’ to compare. It’s fast enough for my sample of five products, but in production it might need to support 100 products or more. So I would prefer to narrow down the key and use ‘IN’ to compare against an array of values. But for now, it works!