Generate Magento 2 Product Feed with MySQL Query

Question

I hope you all having a nice day.

I am wondering is it possible to generate below feed with SQL on Magento 2 Database? I want to run this query from Google Apps scripts using jdbc:mysql.

Expected fields are:

----------------------------------------
| product_id | sku | name | categories |
----------------------------------------

I have tried this way but I don't know how to get product category names (including subcategories) not ids.

SELECT entity_id, GROUP_CONCAT(category_id) as category_ids FROM (
SELECT `e`.entity_id, `at_category_id`.`category_id` 
FROM `catalog_product_entity` AS `e` 
LEFT JOIN `catalog_category_product` AS `at_category_id`
ON (at_category_id.`product_id`=e.entity_id)
) sub_query
GROUP BY entity_id

Thanks :)

Welcome to Stack Overflow! You seem to be asking for someone to write some code for you. Stack Overflow is a question and answer site, not a code-writing service. Please see here to learn how to write effective questions. — Philipp Sander
– Philipp Sander, Commented Feb 7, 2019 at 16:20
the product names can be found in the table catalog_product_entity_varchar this is because of the EAV pattern. — Philipp Sander
– Philipp Sander, Commented Feb 7, 2019 at 16:31
a product can have different names depending on the store-view. so how would you like to display this in the tables? — Philipp Sander
– Philipp Sander, Commented Feb 7, 2019 at 16:33

Khaled Bin Amir · Accepted Answer · 2019-02-07 17:51:59Z

Just solved. Thanks to Philipp Sander, who helped me to do so.

Here is the Query:

SELECT 
  e.entity_id AS product_id
  , e.sku,
  (
    SELECT
      value
    FROM 
      catalog_product_entity_varchar
    WHERE
      entity_id = e.entity_id
      AND attribute_id = 73 and store_id = 0
  ) AS name,
  (
    SELECT
      GROUP_CONCAT(DISTINCT(cv.value))
    FROM 
      catalog_category_entity_varchar AS cv, catalog_category_product AS at_category_id 
    WHERE
      at_category_id.category_id = cv.entity_id
      AND (at_category_id.product_id = e.entity_id) 
      AND cv.attribute_id = 45 and cv.store_id = 0
  ) AS categories 
FROM catalog_product_entity AS e 
ORDER BY product_id ASC;

Stack Exchange Network

Generate Magento 2 Product Feed with MySQL Query

1 Answer 1

Your Answer

Hot Network Questions

Generate Magento 2 Product Feed with MySQL Query

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions