Get Grouped Product's Children from Database

Question

I'm looking for a quick way to see all of a grouped product's children from the database. I can see all of a configurable product's children with this query:

select cpe_parent.entity_id as 'Parent ID', cpe_parent.sku as 'Parent Sku', cpe_child.entity_id as 'Child ID', cpe_child.sku as 'Child Sku'
from catalog_product_entity cpe_parent
join catalog_product_super_link cpsl on cpe_parent.row_id = cpsl.parent_id and cpe_parent.type_id = 'configurable'
left join catalog_product_entity cpe_child on cpsl.product_id = cpe_child.entity_id
order by cpe_parent.sku, cpe_child.sku;

I'd like to have something similar for grouped products but they aren't linked to their children through the catalog_product_super_link table.

Answer 1

Grouped products are linked to their children through the catalog_product_link table with a link_id of 3. Here's a query to grab all grouped products and their children:

-- Grouped Products
select cpe_parent.entity_id as 'Parent ID', cpe_parent.sku as 'Parent Sku', cpe_child.entity_id as 'Child ID', cpe_child.sku as 'Child Sku'
from catalog_product_entity cpe_parent
join catalog_product_link cpl on cpe_parent.type_id = 'grouped' and cpl.link_type_id = 3 and cpe_parent.row_id = cpl.product_id
left join catalog_product_entity cpe_child on cpl.linked_product_id = cpe_child.entity_id
order by cpe_parent.sku, cpe_child.sku;

Related, Upsell, and Cross sell products are also linked through this table. Here are the queries for those

-- Related Proudcts
select cpe_parent.entity_id as 'Parent ID', cpe_parent.sku as 'Parent Sku', cpe_child.entity_id as 'Child ID', cpe_child.sku as 'Child Sku'
from catalog_product_entity cpe_parent
join catalog_product_link cpl on cpl.link_type_id = 1 and cpe_parent.row_id = cpl.product_id
left join catalog_product_entity cpe_child on cpl.linked_product_id = cpe_child.entity_id
order by cpe_parent.sku, cpe_child.sku;

-- Upsell Products
select cpe_parent.entity_id as 'Parent ID', cpe_parent.sku as 'Parent Sku', cpe_child.entity_id as 'Child ID', cpe_child.sku as 'Child Sku'
from catalog_product_entity cpe_parent
join catalog_product_link cpl on cpl.link_type_id = 4 and cpe_parent.row_id = cpl.product_id
left join catalog_product_entity cpe_child on cpl.linked_product_id = cpe_child.entity_id
order by cpe_parent.sku, cpe_child.sku;

-- Cross sell Products
select cpe_parent.entity_id as 'Parent ID', cpe_parent.sku as 'Parent Sku', cpe_child.entity_id as 'Child ID', cpe_child.sku as 'Child Sku'
from catalog_product_entity cpe_parent
join catalog_product_link cpl on cpl.link_type_id = 5 and cpe_parent.row_id = cpl.product_id
left join catalog_product_entity cpe_child on cpl.linked_product_id = cpe_child.entity_id
order by cpe_parent.sku, cpe_child.sku;

Answer 2

Quick update to make this work on M2.4+

replace cpe_child.row_id with cpe_child.entity_id

select cpe_parent.entity_id as 'Parent ID', cpe_parent.sku as 'Parent Sku', cpe_child.entity_id as 'Child ID', cpe_child.sku as 'Child Sku' from catalog_product_entity cpe_parent join catalog_product_link cpl on cpe_parent.type_id = 'grouped' and cpl.link_type_id = 3 and cpe_parent.entity_id = cpl.product_id left join catalog_product_entity cpe_child on cpl.linked_product_id = cpe_child.entity_id order by cpe_parent.sku, cpe_child.sku