0

Is there any way to find out a MySQL query that would give the same results as exporting all shop products?

The reason I need this. When trying to export, I get the error

Message is added to queue, wait to get your file soon

But even after the cron is run it never appears.

Thanks in advance

Edit: Running MySQL 5.7.27 and Magento 2.3.2

  • Specify Magento version and what also what MySql Query use? – Amit Bera Jul 23 at 10:53
0

try below 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 ;
  • Thanks a lot. Looks good! – Conk1 Sep 16 at 9:40

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.