1

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

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

2 Answers 2

1

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

While your intention was to ask for a SQL query that works the same way as the export process, which can be used for learning and deeper understanding how Magento 2 works purpose (which is good), I should state that there is a more conventional way to get your export file without having to deal with the hassles of manually running the query yourself.

Please take a look at this documentation.

Should you not see your export file after the message

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

You can always execute this command below from your terminal, at the root of your project:

bin/magento queue:consumers:start exportProcessor

This will kick start the consumer queue that includes your export request. After a bit of wait time, you should be able to see your export file from Admin panel (where you submit your export request), or in your filesystem, at <project_root>/var/export/.

The above command can take forever to complete and will not return the typing cursor for you, but once you get your file, you can always cancel the command by using Ctrl+C combo keys, totally safe to do.

Happy coding!

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.