How Can I Get Most of Data for Product Detail Page on MySQL?

[makoto_matsumot]

It seems like Magento 2's product table on database is kinda complicated compared with other major platforms because the main product table doesn't have many columns so many tables need to be joined.

Is there any way or sql that I can get all info on product detail page?

[Vimal Kumar · Accepted solution]

HI @makoto_matsumot,

First thing, Magento 2 use EAV models for Product data. 

Product data are saved in multiple tables. 

catalog_product_entity
catalog_product_entity_int
catalog_product_entity_varchar
catalog_product_entity_text
catalog_product_entity_decimal
catalog_product_entity_datetime

It is not straight forward to write mysql to get product data. 

I always recommend to use Magento models or Magento API to get data.

BTW you can get data using below mysql query. 

SELECT e.entity_id AS 'id',
       v1.value AS 'name',
       e.sku,
       d1.value AS 'price',
       t1.value AS 'short_description',
       t2.value AS 'description',
       v2.value AS 'image',
       v3.value AS 'thumbnail',
       mg.value AS 'media_gallery',
       cids.category_ids AS 'category_ids',
       cids.category_names AS 'category_names'
FROM catalog_product_entity e
LEFT JOIN catalog_product_entity_varchar v1 ON e.entity_id = v1.entity_id
AND v1.store_id = 0
AND v1.attribute_id =
  (SELECT attribute_id
   FROM eav_attribute
   WHERE attribute_code = 'name'
     AND entity_type_id =
       (SELECT entity_type_id
        FROM eav_entity_type
        WHERE entity_type_code = 'catalog_product'))
LEFT JOIN catalog_product_entity_text t1 ON e.entity_id = t1.entity_id
AND t1.store_id = 0
AND t1.attribute_id =
  (SELECT attribute_id
   FROM eav_attribute
   WHERE attribute_code = 'short_description'
     AND entity_type_id =
       (SELECT entity_type_id
        FROM eav_entity_type
        WHERE entity_type_code = 'catalog_product'))
LEFT JOIN catalog_product_entity_text t2 ON e.entity_id = t2.entity_id
AND t2.store_id = 0
AND t2.attribute_id =
  (SELECT attribute_id
   FROM eav_attribute
   WHERE attribute_code = 'description'
     AND entity_type_id =
       (SELECT entity_type_id
        FROM eav_entity_type
        WHERE entity_type_code = 'catalog_product'))
LEFT JOIN catalog_product_entity_varchar v2 ON e.entity_id = v2.entity_id
AND v2.store_id = 0
AND v2.attribute_id =
  (SELECT attribute_id
   FROM eav_attribute
   WHERE attribute_code = 'image'
     AND entity_type_id =
       (SELECT entity_type_id
        FROM eav_entity_type
        WHERE entity_type_code = 'catalog_product'))
LEFT JOIN catalog_product_entity_varchar v3 ON e.entity_id = v3.entity_id
AND v3.store_id = 0
AND v3.attribute_id =
  (SELECT attribute_id
   FROM eav_attribute
   WHERE attribute_code = 'thumbnail'
     AND entity_type_id =
       (SELECT entity_type_id
        FROM eav_entity_type
        WHERE entity_type_code = 'catalog_product'))
LEFT JOIN catalog_product_entity_decimal d1 ON e.entity_id = d1.entity_id
AND d1.store_id = 0
AND d1.attribute_id =
  (SELECT attribute_id
   FROM eav_attribute
   WHERE attribute_code = 'price'
     AND entity_type_id =
       (SELECT entity_type_id
        FROM eav_entity_type
        WHERE entity_type_code = 'catalog_product'))
LEFT JOIN
  (SELECT m1.entity_id,
          GROUP_CONCAT(m2.value) AS value
   FROM catalog_product_entity_media_gallery_value_to_entity m1
   INNER JOIN catalog_product_entity_media_gallery m2 ON m2.value_id = m1.value_id
   AND m2.attribute_id =
     (SELECT attribute_id
      FROM eav_attribute
      WHERE attribute_code = 'media_gallery'
        AND entity_type_id =
          (SELECT entity_type_id
           FROM eav_entity_type
           WHERE entity_type_code = 'catalog_product'))
   GROUP BY m1.entity_id) mg ON e.entity_id = mg.entity_id
LEFT JOIN
  (SELECT product_id,
          GROUP_CONCAT(c.category_id SEPARATOR ',') AS category_ids,
          GROUP_CONCAT(cv.value SEPARATOR ',') AS category_names
   FROM catalog_category_product c
   INNER JOIN catalog_category_entity_varchar cv ON c.category_id = cv.entity_id
   AND cv.store_id = 0
   AND cv.attribute_id =
     (SELECT attribute_id
      FROM eav_attribute
      WHERE attribute_code = 'name'
        AND entity_type_id =
          (SELECT entity_type_id
           FROM eav_entity_type
           WHERE entity_type_code = 'catalog_category'))
   GROUP BY product_id) cids ON e.entity_id = cids.product_id ;

Mysql query reference. productdata-mysql-query

I hope it will help you!