1

In WooCommerce, I would like to filter my products page based on product variation's meta and related variable products's taxonomy.

Note: All products have variations, there's no product without variations.

For an example, I would like to be able to filter products by categories of related variable products (Bracelets, Earrings, Rings...) and their variation's color meta (Gold, Silver, Bronze...)

The problem is combining the taxonomy and product variations is not working. To filter by taxonomy I am using the following code:

    $args = array (
    'post_type' => array ('product'),
    'post_status' => 'publish',
    'order_by' => 'date',
    'order' => 'DSC',
    'posts_per_page' => 18,
    'tax_query' => $tax_query
);

This is working and give me the correct results.

To filter the variation's meta, I used this code (it's working as well):

    $args2 = array (
    'post_type' => array ('product_variation'),
    'post_status' => 'publish',
    'order_by' => 'date',
    'order' => 'DSC',
    'posts_per_page' => 18,
    'meta_query' => $meta_query
);

Now, what is not working is combining both together. I think it because product variations don't have taxonomy so I might be doing something wrong with the logic. Here what I tried and its not working:

    $args = array (
    'post_type' => array ('product', 'product_variation'),
    'post_status' => 'publish',
    'order_by' => 'date',
    'order' => 'DSC',
    'posts_per_page' => 18,
    'tax_query' => $tax_query,
    'meta_query' => $meta_query
);

When I do this I get "no product found" even thought that my filters must return something.

Any help is much appreciated. Thanks

1

You can try using $wpdb, like this:

// Use the global variable $wpdb;
global $wpdb;

// :: Define SQL command ::

// You can request for the IDs only,
// and then get the properties later on via get_post_meta or WC_Product()
$q  = 'SELECT wp_posts.ID ';
$q .= 'FROM wp_posts ';

// Attach wp_postmeta table
$q .= 'JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) ';

// Attach taxonomy and term tables
$q .= 'JOIN wp_term_relationships ON ( wp_posts.ID = wp_term_relationships.object_id ) ';
$q .= 'JOIN wp_term_taxonomy ON ( wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id ) ';
$q .= 'JOIN wp_terms ON ( wp_term_taxonomy.term_id = wp_terms.term_id ) ';

// WHERE statements
$q .= 'WHERE ';

    // Post should be published
    $q .= 'wp_posts.post_status =  "publish" ';

    $q .= 'AND ';

    // Post should have either product or product_variation as post_type
    $q .= '(';
        $q .= 'wp_posts.post_type =  "product" ';
        $q .= 'OR ';
        $q .= 'wp_posts.post_type =  "product_variation" ';
    $q .= ')';

    $q .= 'AND ';

    // Post should have ##color_meta_key## = ##color_meta_value##
    $q .= '(';
        $q .= 'wp_postmeta.meta_key =  "##color_meta_key##" ';
        $q .= 'AND ';
        $q .= 'wp_postmeta.meta_value =  "##color_meta_value##" ';
    $q .= ')';

    $q .= 'AND ';

    // Post should belong to taxonomy_term with ##taxonomy_term_id## as ID
    $q .= 'wp_term_taxonomy.term_id IN ( ##taxonomy_term_id## ) ';

// Ordered according to date, descending
$q .= 'ORDER BY wp_posts.post_date DESC ';

// Limit the result to 18
$q .= 'LIMIT 18 '

// Will return an object
$wpdb->get_results($q);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.