I want to get product list with category and category listed as in two different columns in woocommerce

Question

i want to get product list with parent category and subcategory as seperate in the result in woocommerce

This code I have tried

SELECT
p.ID,
 p.post_title,
 `post_content`,
`post_excerpt`,
t.name AS product_category,
t.term_id AS product_id,
t.slug AS product_slug,
tt.term_taxonomy_id AS tt_term_taxonomia,
tr.term_taxonomy_id AS tr_term_taxonomia,
MAX(CASE WHEN pm1.meta_key = '_price' then pm1.meta_value ELSE NULL END) as price,
MAX(CASE WHEN pm1.meta_key = '_regular_price' then pm1.meta_value ELSE NULL END) as 
regular_price,
MAX(CASE WHEN pm1.meta_key = '_sale_price' then pm1.meta_value ELSE NULL END) as sale_price,
MAX(CASE WHEN pm1.meta_key = '_sku' then pm1.meta_value ELSE NULL END) as sku
FROM wp_posts p
LEFT JOIN wp_postmeta pm1 ON pm1.post_id = p.ID
LEFT JOIN wp_term_relationships AS tr ON tr.object_id = p.ID
JOIN wp_term_taxonomy AS tt ON tt.taxonomy = 'product_cat' AND tt.term_taxonomy_id = 
tr.term_taxonomy_id
JOIN wp_terms AS t ON t.term_id = tt.term_id
WHERE p.post_type in('product', 'product_variation') AND p.post_status = 'publish' AND 
p.post_content <> ''
GROUP BY p.ID,p.post_title

Any Idea,thanks in advance

Answer 1

If you want to get a list of products with parent_category and sub_category in an array format, then you can use WordPress WP_Query. Try following code snippet.

<?php
    // fetch all products from woocommerce
    $args = array('post_type' => 'product', 'posts_per_page' => -1); 
    $loop = new WP_Query($args);

    $product_list_array = []; // result array with product_id,parent_category and sub_category

    while ($loop->have_posts()) : $loop->the_post();
        global $product;
        $product_id = $product->get_id();
        $result = []; $parent_cats = []; $sub_cats = []; 
        $result['product_id'] = $product_id;
        $categories = get_the_terms($product_id, 'product_cat'); // get category list of a product
        foreach ($categories as $category) {
            // check if it is a parent or child
            if ($category->parent == 0) {
                $parent_cats[] = $category->name;
            } else {
                $parent_cat = get_term_by('id', $category->parent, 'product_cat'); // to get parent category name 
                $sub_cats[] = $category->name;
                $parent_cats[] = $parent_cat->name;
            }
        }
    
        // if multiple category exist then join them to a string
        $result['parent_category'] = implode(" | ", array_unique($parent_cats));
        $result['sub_category'] = implode(" | ", array_unique($sub_cats));
        $product_list_array[] = $result;
    endwhile;
?>

The $product_list_array should contain product_id,parent_category and sub_category.