1

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 :)

6
  • 1
    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. Feb 7 '19 at 16:20
  • I am sorry. I didn't know that. What should I do now? Feb 7 '19 at 16:24
  • try something on your own and post your attempt Feb 7 '19 at 16:30
  • the product names can be found in the table catalog_product_entity_varchar this is because of the EAV pattern. Feb 7 '19 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? Feb 7 '19 at 16:33
2

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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