MySQL grouping different rows to create group and simple products

Question

I am migrating products from one system to Magento and there is a group/child products relation in an old system and I have to maintain in the Magento System

The current table structure I have:

[id],   [sku],  [name],         [parent id]
202182 |240330 |parent product |
202183 |240331 |child product 1|[202182][Parent-Product-240330]
202184 |240332 |child product 2|[202182][Parent-Product-240330]

What i want to achieve:

[id],   [sku],   [name],         [product_type], [associated_sku]
202182 |240330 | parent product |grouped        |240331=0.0000,240332=0.0000
202183 |240331 | child product 1|simple         |
202184 |240332 | child product 2|simple         |

Can you please help me to write a query to achieve it.

Answer 1

If the hierarchy has only two levels, it's perfectly possible to do it in MySQL 5.6, so you are good as you are (if it had a variable number of levels you would need MySQL 8.x).

The query I would use is:

select 
  p.id,
  p.sku,
  p.name,
  'grouped' as product_type,
  group_concat (concat(c.id, '=0.0000') order by c.id) as associated_sku
from product p
join product c on c.parent_id = p.id
group by p.id, p.sku, p.name
union all
select
  id,
  sku,
  name,
  'simple' as product_type,
  null as associated_sku
from product
where parent_id is not null

Note: This query assumes the value of parent_id is null on parent rows.