CODEWITHSUNDEEP
phpmysqlsqlwoocommercecustom-taxonomy
cham
cham

Reputation: 31

Get product ids from specific product attributes values using SQL in Woocommerce

I am trying to get product ids where product attributes pa_color = 'Black' and pa_waterproof = 'Yes'. But I have not succeeded yet.

This is my code:

global $wpdb;

$product_id = $wpdb->get_results( "
    SELECT post_id 
    FROM $wpdb->postmeta 
    WHERE meta_key='pa_color' 
    AND meta_value='Black' 
    AND meta_key='pa_waterproof' 
    AND meta_value='Yes' 
" );

Any help is appreciated

Upvotes: 1

Views: 3065

Answers (1)

LoicTheAztec
LoicTheAztec

Reputation: 253824

Update (related to your comment)

You are may be trying to get the variable product ID instead which is not the same thing.

Then you can use normal product attribute taxonomy and term names values (or even term Ids or term slugs).

In this case try the following:

global $wpdb;

$results = $wpdb->get_col( "
    SELECT p.ID
    FROM {$wpdb->prefix}posts as p
    INNER JOIN {$wpdb->prefix}term_relationships as tr ON p.ID = tr.object_id
    INNER JOIN {$wpdb->prefix}term_taxonomy as tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
    INNER JOIN {$wpdb->prefix}terms as t ON tt.term_id = t.term_id
    INNER JOIN {$wpdb->prefix}term_relationships as tr2 ON p.ID = tr2.object_id
    INNER JOIN {$wpdb->prefix}term_taxonomy as tt2 ON tr2.term_taxonomy_id = tt2.term_taxonomy_id
    INNER JOIN {$wpdb->prefix}terms as t2 ON tt2.term_id = t2.term_id
    WHERE p.post_type LIKE 'product'
    AND p.post_status LIKE 'publish'
    AND tt.taxonomy LIKE 'pa_color'
    AND t.name = 'Black'
    AND tt2.taxonomy LIKE 'pa_waterproof'
    AND t2.name = 'Yes'
");

// Raw output
echo '<pre>'; print_r($results); echo '</pre>';

Tested and works

First answer.

Get product variation ids from specific product attribute values using SQL in Woocommerce

In the data base the product attribute registered terms for product variations in the post_meta table are term slugs (not term names) and all product attributes meta_key start with attribute_pa_ instead of pa_.

So it will be attribute_pa_color = 'black' and attribute_pa_waterproof = 'yes' instead.

Also you need to make a SQL query on wp_posts table for product_variation post type Joining 2 times the wp_postmeta table to query each product attribute.

So try:

global $wpdb;

$variation_id = $wpdb->get_var( "
    SELECT p.ID FROM {$wpdb->prefix}posts as p
    INNER JOIN {$wpdb->prefix}postmeta as pm ON p.ID = pm.post_id
    INNER JOIN {$wpdb->prefix}postmeta as pm2 ON p.ID = pm2.post_id
    WHERE p.post_type LIKE 'product_variation'
    AND p.post_status LIKE 'publish'
    AND pm.meta_key LIKE 'attribute_pa_color'
    AND pm.meta_value = 'black'
    AND pm2.meta_key LIKE 'attribute_pa_waterproof'
    AND pm2.meta_value = 'yes'
");

// Output
echo $variation_id . '<br>';

Tested and works

Upvotes: 2

Related Questions