0

I'm running a Magento 2.3 and I would like to run a query in my database to export some products data that I don't have natively via the admin panel, the attributes are:

  • Status
  • Stock
  • Images

How could I get it via SQL?

0

You can run this query bellow to have this data, I checked and set the attribute_id manually, so you will need to check it and change it to your attribute_id.

SELECT `e`.*,
       `at_status`.`value`         AS `status`,
       `at_price`.`value`          AS `price`,
       `at_image1`.value           as `image1`,
       `at_image2`.value           as `image2`,
       `at_small_image`.value      as `small image`,
       `at_swatch_image`.value     as `swatch_image`
FROM `catalog_product_entity` AS `e`
         INNER JOIN `catalog_product_entity_int` AS `at_status`
                    ON (`at_status`.`row_id` = `e`.`row_id`) AND (`at_status`.`attribute_id` = '97') AND
                       (`at_status`.`store_id` = 0)
         INNER JOIN `catalog_product_entity_decimal` AS `at_price`
                    ON (`at_price`.`row_id` = `e`.`row_id`) AND (`at_price`.`attribute_id` = '77') AND
                       (`at_price`.`store_id` = 0)
         INNER JOIN `inventory_source_item` as `at_stock`
                    ON at_stock.sku = e.sku
         LEFT JOIN `catalog_product_entity_varchar` AS `at_image1`
                   ON (`at_image1`.`row_id` = `e`.`row_id`) AND (`at_image1`.`attribute_id` = '48') AND
                      (`at_image1`.`store_id` = 0)
         LEFT JOIN `catalog_product_entity_varchar` AS `at_image2`
                   ON (`at_image2`.`row_id` = `e`.`row_id`) AND (`at_image2`.`attribute_id` = '87') AND
                      (`at_image2`.`store_id` = 0)
         LEFT JOIN `catalog_product_entity_varchar` AS `at_small_image`
                   ON (`at_small_image`.`row_id` = `e`.`row_id`) AND (`at_small_image`.`attribute_id` = '88') AND
                      (`at_small_image`.`store_id` = 0)
         LEFT JOIN `catalog_product_entity_varchar` AS `at_swatch_image`
                   ON (`at_swatch_image`.`row_id` = `e`.`row_id`) AND (`at_swatch_image`.`attribute_id` = '175') AND
                      (`at_swatch_image`.`store_id` = 0)
WHERE (at_price.value >= 0)
  and at_status.value = '1'
  and at_stock.status = 1
  and (at_image1.value is null or at_image1.value IN ('', 'no selection'))
  and (at_image2.value is null or at_image2.value IN ('', 'no selection'))
  and (at_small_image.value is null or at_small_image.value IN ('', 'no selection'))
  and (at_swatch_image.value is null or at_swatch_image.value IN ('', 'no selection'))

Then you can save it in a SQL file and run it to generate a CSV.

mysql -h 127.0.0.1 -u magentouser -pmagentopassword magentodbname < products-data.sql > products-data.csv

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.