Question 3524 in Woocommerce Question posted in
The official Woocommerce documentation can be found here.

SQL query based on two columns – Woocommerce

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).

id item_id parent_id other_columns
1 aaa aaa …
2 aaa-1 aaa …
3 aaa-2 aaa …
4 bbb bbb …
5 bbb-1 bbb …
6 ccc ccc …
7 ddd ddd …

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.

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0 
    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search