Magento 2 – Get Product Info from MySQL Query

databasemagento2

I'm currently working on a Backend-like Page for special Customers within our shop. Theese special customers should be able to export product-data for a predefinied Shopsystem and other internal (shop related) stuff.

I was able to create a query with a thousands while statements but it looks pretty bad and is hard to understand / edit for someone with no understanding.

I road about MYSQL inner join statements and stuff like that but got never into it.

Has anyone a query to get the following data?

  • Product Name
  • Product SKU
  • Product Price
  • Short-description
  • Description
  • Main & Thumbnail image
  • Additional images
  • Product Categories

Or could explain / link ressources to get to a result like this ?

EDIT – 08/09/2017

I road a few documentaions and learned some new stuff. My query currently looks like this:

SELECT 
    catalog_product_entity.entity_id AS id,
    catalog_product_entity.type_id AS type,
    catalog_product_entity.attribute_set_id AS attribute_id, 
    catalog_product_entity.sku AS sku, 
    catalog_product_entity_varchar.value AS name,
    catalog_product_entity_decimal.value AS price
FROM catalog_product_entity
    LEFT JOIN catalog_product_entity_varchar ON catalog_product_entity.entity_id = catalog_product_entity_varchar.entity_id
    LEFT JOIN catalog_product_entity_decimal ON catalog_product_entity.entity_id = catalog_product_entity_decimal.entity_id
WHERE 
    catalog_product_entity_varchar.attribute_id = "71" AND 
    catalog_product_entity_varchar.store_id = "0" AND
    catalog_product_entity_decimal.attribute_id = "75";

So far so good, everything works like i want it to be. The only part I dont understand ist the part where i have to select a new entry from catalog_product_entity_varchar to get the description and other attributes

Anything i can read about that ? Anyone who can help ?

Best Answer

First, it is not that simple, Magento database modeling is in eav (entity,attribute,value) so it requires a lot of JOINS

I recommand doing this by passing through Magento collections (in php script)

But anyway here is the MySQL query that responds to your need :

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 ;

Try it and let me know if it helped you.

Related Topic