I have a quite large products table and I need some smart solution to find out which products are (in woocommerce terms) Simple, Variable, and Variation.

In the example table below, ids 6 and 7 are Simple products (they don't have children), 1 and 4 are Variable products (they have children), and 2,3,5 are Variations (they are children).

My goal is to first get Simple products, then get Variable products and import them and lastly get Variations and import them to the existing parent Variable products.

I have a solution for Simple products:

SELECT *
FROM (
    SELECT *
    FROM $table_name
    WHERE item_id = parent_id
    GROUP BY parent_id
    HAVING COUNT(*) = 1
) AS ONLY_ONCE

I've tried similar for the Variable products (count(*) > 1), but it doesn't work...

For Variation I have:

SELECT * 
FROM $table_name 
WHERE item_id != parent_id

1. Simple products - select all where item_id is equal with parent_id, and parent_id occurs only once.
2. Variable products - select all where item_id is equal with parent_id and parent_id occurs more than once.
3. Variation - select all where item_id and parent_id are not equal.

How could I write those three queries, especially the second one? any help is appreciated.

CodePudding user response：

SELECT *,
       CASE WHEN EXISTS ( SELECT NULL
                          FROM test t2
                          WHERE t2.parent_id = t1.item_id
                            AND t2.item_id <> t2.parent_id )
            THEN 'Variable'
            WHEN NOT EXISTS ( SELECT NULL
                              FROM test t3
                              WHERE t3.parent_id = t1.item_id )
            THEN 'Variation'
            ELSE 'Simple'
            END Category
FROM test t1;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=b3215cbb3febbdbc4132ee00aac2ad8e

CodePudding user response：

I would solve this as a combination of SQL queries and php code.

At first I would get all parents (variable products) and fetch the results. In PHP then loop through the results and use the parents id as parameter for the next query.