1

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 ?

  • For every attribute you need to add a join based on its id to get it so if you want to get 5 (non static) attributes you need at least 5 joins. See my answer below. – Mohamed23gharbi Sep 10 '17 at 12:51
9

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.

  • I definitly have to learn more SQL. It worked like a charm and i was able to add a few things that i missed on my question. Thank you very much! – Marcel H. Sep 12 '17 at 9:08
  • Glad it helped you, you are welcome – Mohamed23gharbi Sep 12 '17 at 10:31
  • Showing as sql error – jafar pinjar Oct 4 '18 at 14:19
  • @jafar pinjar The query has no error, please check that your running it on Magento2 database and not Magento1 – Mohamed23gharbi Oct 5 '18 at 14:41
0

try this one, it worked faster. Note that you might have to change the attribute ids by looking them in eav_attribute table. Feel free to change where clause conditions as per your need.

select cpe.entity_id,
 cpe.sku,
 cpev.value as name,
    cpr.`parent_id`,
   cpe1.sku as parent_sku,
  cpet1.value as short_description, 
  cpev2.value as url_key, 
  cpev3.value as brand,
   cpei.value as status,
   cpei1.value as visibility,
   (select class_name from tax_class where class_id = cpei2.value and class_type='PRODUCT') as tax_class, 
   (select value from eav_attribute_option_value eaov where option_id=cpei3.value and store_id=0) as include_in_feed,
   cped.value as price,
   cped1.value as special_price,
   mg.value as media_gallery,
   cids.category_ids,
   cids.category_names,
   cids.category_paths
   from catalog_product_entity cpe 
left join catalog_product_entity_varchar as cpev on cpev.entity_id=cpe.entity_id and cpev.attribute_id=73 -- name --
left join catalog_product_entity_text as cpet1 on cpet1.entity_id=cpe.entity_id and cpet1.attribute_id=76 -- short description --
left join catalog_product_entity_varchar as cpev2 on cpev2.entity_id=cpe.entity_id and cpev2.attribute_id=119 -- url key --
left join catalog_product_entity_varchar as cpev3 on cpev3.entity_id=cpe.entity_id and cpev3.attribute_id=136 -- brand --
left join catalog_product_entity_int as cpei on cpei.entity_id=cpe.entity_id and cpei.attribute_id=97 -- status --
left join catalog_product_entity_int as cpei1 on cpei1.entity_id=cpe.entity_id and cpei1.attribute_id=99 -- visibility --
left join catalog_product_entity_int as cpei2 on cpei2.entity_id=cpe.entity_id and cpei2.attribute_id=134 -- tax class --
left join catalog_product_entity_int as cpei3 on cpei3.entity_id=cpe.entity_id and cpei3.attribute_id=184 -- include in feed --
left join catalog_product_entity_decimal as cped on cped.entity_id=cpe.entity_id and cped.attribute_id=77 -- price --
left join catalog_product_entity_decimal as cped1 on cped1.entity_id=cpe.entity_id and cped1.attribute_id=78 -- specials price --
left join catalog_product_relation cpr on cpr.child_id=cpe.entity_id left join catalog_product_entity cpe1 on cpe1.entity_id=cpr.parent_id
left join cataloginventory_stock_status css on cpe.entity_id=css.product_id 
LEFT JOIN 
  (SELECT product_id,
          GROUP_CONCAT(distinct c.category_id SEPARATOR ',') AS category_ids,
          GROUP_CONCAT(distinct cv.value  SEPARATOR ',') AS category_names,
          GROUP_CONCAT(distinct cv1.value SEPARATOR ',') AS category_paths
   FROM catalog_category_product c
   INNER JOIN catalog_category_entity_varchar cv ON c.category_id = cv.entity_id and cv.attribute_id=45 and cv.store_id=0
   INNER JOIN catalog_category_entity_varchar cv1 ON c.category_id = cv1.entity_id and cv1.attribute_id=118 and cv.store_id=0
   GROUP BY product_id) cids ON cpe.entity_id = cids.product_id
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`=90
   GROUP BY m1.entity_id) mg ON cpe.entity_id=mg.entity_id
where cpe.type_id='simple' and css.stock_status=1 and css.qty>0 limit 1000 offset 10;

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.