Woocommerce Mysql query to fetch products and varables-CodePudding

Trying to fetch products ID and the products variable ID in Mysql.

My category is 7006. This query gives me only the general product, and not the product variables values.

SELECT post.ID, post.post_title, metavalue1.meta_value AS MetaValue1, metavalue2.meta_value  AS MetaValue2
FROM wp_posts post
LEFT JOIN wp_postmeta metavalue1 ON post.ID = metavalue1.post_id 
AND '_enable_colorlab' = metavalue1.meta_key
LEFT JOIN wp_postmeta  metavalue2 ON post.ID = metavalue2.post_id 
AND '_wcpa_product_meta' = metavalue2.meta_key 
LEFT JOIN wp_term_relationships rs ON rs.object_id = post.ID 
WHERE rs.term_taxonomy_id  ='7006';

This query gives me all variables ID i need

SELECT post.ID, post.post_title FROM wp_posts post   
INNER JOIN wp_postmeta pa ON pa.post_id = post.ID   
INNER JOIN wp_term_relationships rs ON rs.object_id = post.ID  
WHERE  rs.term_taxonomy_id  ='7006';

How can i get first query to include all product and variables values and not the general product value?

CodePudding user response：

You can try something like this:

select 
    p.id,
    p.post_title,
    group_concat(concat(m.meta_key, ':', m.meta_value))
from 
    wp_posts as p
    left join wp_postmeta as m on m.post_id = p.id
    left join wp_term_relationships rs on rs.object_id = p.id
where
    rs.term_taxonomy_id = '7006'
group by
    p.id, 
    p.post_title

In PHP you can explode concatenated string of values

CodePudding user response：

IT will return variation products with variant id.

SELECT wp.id AS `Product Id`, wpv.id AS `Variant Id`, wp.post_title, wpv.post_title, wpv.post_excerpt 
FROM wp_posts wp 
INNER JOIN wp_term_relationships r ON wp.ID = r.object_id 
INNER JOIN wp_term_taxonomy tt ON r.term_taxonomy_id = 7006
INNER JOIN wp_terms t ON t.term_id = tt.term_id 
INNER JOIN wp_posts wpv ON wp.id = wpv.post_parent 
LEFT JOIN wp_postmeta wpm ON wp.ID = wpm.post_id 
WHERE tt.taxonomy = 'product_type' 
AND t.name = 'variable' 
AND wpv.post_type != 'attachment'
AND wpm.meta_key = '_enable_colorlab'