4

I'm trying to get which table saves the Related Products for items and order-items? Is there any way to know how many items sold by these property. I try to check this table sales_order_item but I don't find anything?

3 Answers 3

3

Currently there is no table which saves the data to check if a products was added through cross-sell or up-sell property.

But for related products, the information is stored in product_options column of sales_order_item table. You can find related_product key in the json of info.

I don't know why people are downvoting it without specifying reasons. The questions does not ask where the product linking is saved. The question clearly ask if there is a way we can find out if the product added to cart/order was through upsell, cross sell or related product property.

0
2

For products the linked products are saved in catalog_product_link The column catalog_product_link_type stores whether it's a cross/up/down sell.

You can use the following query to get an overview of the linked product sku's and the relation type

SELECT `link`.`link_id` as `link_id`,
`type`.`code` as `link_type`,
`source`.`sku` as `source_sku`,
`target`.`sku` as `target_sku`
FROM `disposable_shop`.`catalog_product_link` as `link`
join `disposable_shop`.`catalog_product_link_type` as type on `link`.`link_type_id` = `type`.`link_type_id`
join `catalog_product_entity` as `source` on `source`.`entity_id` = `link`.`product_id`
join `catalog_product_entity` as `target` on `target`.`entity_id` = `link`.`linked_product_id`;
3
  • This is not what the question asks. Question is if there is a way we can identify if a product added to cart/order was through upsell or cross sell or through related product. Commented Jul 8, 2023 at 19:50
  • @Afreen Sheikh – yes, in part. The first question is "which table saves the Related Products", so this answer is imo okay. But there is a syntax error, you have to remove the last comma in the field list.
    – digijay
    Commented Jan 4 at 7:50
  • 1
    @digijay Thanks for letting me know. Fixed the answer. Commented Jan 5 at 11:37
1
SELECT
    p1.sku AS product_sku,
    GROUP_CONCAT(DISTINCT p2.sku ORDER BY p2.entity_id SEPARATOR ',') AS related_skus
FROM
    catalog_product_link AS l
    JOIN catalog_product_entity AS p1 ON l.product_id = p1.entity_id
    JOIN catalog_product_entity AS p2 ON l.linked_product_id = p2.entity_id
    JOIN catalog_product_link_type AS lt ON l.link_type_id = lt.link_type_id
WHERE
    lt.code = 'relation'
GROUP BY
    p1.sku;
2
  • This is not what the question asks. Question is if there is a way we can identify if a product added to cart/order was through upsell or cross sell or through related product. Commented Jul 8, 2023 at 19:50
  • nice answer! thx
    – digijay
    Commented Jan 4 at 7:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.