Magento SQL Queries: Grouped and Associated Products

Get a list of grouped products SKUs and their associated:

SELECT 
  cpe_grouped.sku AS grouped_sku, GROUP_CONCAT(cpe_associated.sku ORDER BY cplai.value) AS associated_sku
FROM 
  catalog_product_link cpl
  LEFT JOIN catalog_product_entity cpe_grouped ON cpl.product_id = cpe_grouped.entity_id
  LEFT JOIN catalog_product_entity cpe_associated ON cpl.linked_product_id = cpe_associated.entity_id
  LEFT JOIN catalog_product_link_attribute_int cplai ON cplai.link_id = cpl.link_id AND cplai.product_link_attribute_id = (
    SELECT cpla.product_link_attribute_id FROM catalog_product_link_attribute cpla WHERE cpla.product_link_attribute_code LIKE 'position' AND cpla.link_type_id = (
      SELECT cplt.link_type_id FROM catalog_product_link_type cplt WHERE cplt.code LIKE 'super'
    )
  )
-- FILTER PRODUCTS HERE
GROUP BY cpe_grouped.sku
-- SORT PRODUCTS HERE
;

If you want to out the results to a csv file, you can add at the end of the query:

INTO OUTFILE '/path/to/destination/file.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
;

This is:

SELECT 
  cpe_grouped.sku AS grouped_sku, GROUP_CONCAT(cpe_associated.sku ORDER BY cplai.value) AS associated_sku
FROM 
  catalog_product_link cpl
  LEFT JOIN catalog_product_entity cpe_grouped ON cpl.product_id = cpe_grouped.entity_id
  LEFT JOIN catalog_product_entity cpe_associated ON cpl.linked_product_id = cpe_associated.entity_id
  LEFT JOIN catalog_product_link_attribute_int cplai ON cplai.link_id = cpl.link_id AND cplai.product_link_attribute_id = (
    SELECT cpla.product_link_attribute_id FROM catalog_product_link_attribute cpla WHERE cpla.product_link_attribute_code LIKE 'position' AND cpla.link_type_id = (
      SELECT cplt.link_type_id FROM catalog_product_link_type cplt WHERE cplt.code LIKE 'super'
    )
  )
-- FILTER PRODUCTS HERE
GROUP BY cpe_grouped.sku
-- SORT PRODUCTS HERE
INTO OUTFILE '/path/to/destination/file.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
;

If you don’t use an absolute path, OUTFILE will be relative to mysql database data dir, usually /var/lib/mysql/{dbname}.

One response to “Magento SQL Queries: Grouped and Associated Products

Leave a Reply

Your email address will not be published. Required fields are marked *