Query to get Woo products in a parent-children order

Question

I want to build a MySQL query for a Woo database schema to get simple/variable products, but in a specific order: I want to get them in an ID ASCending order, but when a variable product is met, I want to get the variations exactly below their parent.

So for a simple product, or the parent of a variable product, the post_type field is set to 'product' and the post_parent field is set to 0. For the children of a variable product (a.k.a variations) the post_type field is set to 'variable_product' and the post_parent field is set to the ID of the parent product.

So, imagine this desired order:

ID	post_title	post_type	post_parent
1100	title1	product	0
1104	title2	product	0
1130	title2 - variation1	variable_product	1104
1200	title2 - variation2	variable_product	1104
1208	title2 - variation3	variable_product	1104
1107	title3	product	0
1111	title4	product	0
1205	title4 - variation1	variable_product	1111
1210	title4 - variation4	variable_product	1111
1430	title4 - variation3	variable_product	1111
1432	title4 - variation2	variable_product	1111

So by the above table you see that I want products to be sorted by their ID, until the variation(s) of a product are met, which I want to be placed under their parent, and get sorted themselves by their ID also. So I don't care about titles not being sorted alphabetically (title4-variation4 is sorted above title4 - variation3 because the variations' IDs are sorted in an ascending order).

So I tried to play a little with ORDER BY ID ASC, post_parent ASC and also ORDER BY ID ASC, post_type ASC because product is sorted alphabetically above variation_product, but I couldn't get them sorted correctly. I always end up with parent products being sorted higher than variations, just because their IDs are smaller.

SELECT * FROM wp6c_posts WHERE post_type IN ('product', 'product_variation') ORDER BY ID ASC, post_parent ASC;

Obviously I have to query table wp6c_posts twice, once for the simple/parent products, and then for the variations of the variable products, but I can't think of the correct query.

Can someone help me with this?

Answer 1

you can use the following query. It may fulfils your requirement.

  $args = array(
        'post_per_page' => 1,
        'order'         => 'DESC',
        'post_type'     => ['product', 'variable_product'],
    );

    $products = get_children($args);

    if ($products) {
        foreach ($products as $product) {

        }
    }