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
ASC
ending 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?