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, 2019 at 16:20
  • I am sorry. I didn't know that. What should I do now? Feb 7, 2019 at 16:24
  • try something on your own and post your attempt Feb 7, 2019 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, 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? Feb 7, 2019 at 16:33

1 Answer 1

3

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 and acknowledge you have read our privacy policy.

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