1

I want to get the below data of all products by SQL Query.

  • SKU
  • Product name
  • Website
  • Attribute set
  • Quantity per source
  • Sellable quantity
  • visibility
  • Price
  • Status

I have used this extension: https://github.com/degdigital/magento2-customreports
This extension has functionality to get the CSV report by SQL query. So I want to get product reports by SQL Query.

Any Help much appreciated!

2 Answers 2

0

I have a suggest for you that. First check these table on database you can find the query for all product .

tables are :

catalog_product_entity for sku, catalog_product_entity_varchar for name , eav_attribute_set for attribute set, store_website for website ,cataloginventory_stock_item for quantity and catalog_product_entity_decimal for price .

1
  • Thanks for it..But I want a SQL query for this. I tried to create this but not getting success. Also, "Quantity per Source" and "Sellable Quantity" also look like challenging points for me.
    – Amit Saini
    Mar 25, 2021 at 8:32
0

The title can be different from one store view to an other. Same goes for the description. Also, some store views can use the default values set in the backend.

Here is a full query on how to get the data you need (sku, name, description) for all the products for a specific store view (id 1).

SELECT 

    `e`.`sku`, 
    IF(at_name.value_id > 0, at_name.value, at_name_default.value) AS `name`,
    IF(at_description.value_id > 0, at_description.value, at_description_default.value) AS `description`


FROM 
   `catalog_product_entity` AS `e` 
    INNER JOIN 
         `catalog_product_entity_varchar` AS `at_name_default` 
               ON (`at_name_default`.`entity_id` = `e`.`entity_id`) AND 
                  (`at_name_default`.`attribute_id` = (SELECT attribute_id FROM `eav_attribute` ea LEFT JOIN `eav_entity_type` et ON ea.entity_type_id = et.entity_type_id  WHERE `ea`.`attribute_code` = 'name' AND et.entity_type_code = 'catalog_product')) AND 
                  `at_name_default`.`store_id` = 0 
    LEFT JOIN 
          `catalog_product_entity_varchar` AS `at_name` 
               ON (`at_name`.`entity_id` = `e`.`entity_id`) AND 
                  (`at_name`.`attribute_id` = (SELECT attribute_id FROM `eav_attribute` ea LEFT JOIN `eav_entity_type` et ON ea.entity_type_id = et.entity_type_id  WHERE `ea`.`attribute_code` = 'name' AND et.entity_type_code = 'catalog_product')) AND 
                  (`at_name`.`store_id` = 1) 
    INNER JOIN 
         `catalog_product_entity_text` AS `at_description_default` 
               ON (`at_description_default`.`entity_id` = `e`.`entity_id`) AND 
                  (`at_description_default`.`attribute_id` = (SELECT attribute_id FROM `eav_attribute` ea LEFT JOIN `eav_entity_type` et ON ea.entity_type_id = et.entity_type_id  WHERE `ea`.`attribute_code` = 'description' AND et.entity_type_code = 'catalog_product')) AND 
                  `at_description_default`.`store_id` = 0 
    LEFT JOIN 
          `catalog_product_entity_text` AS `at_description` 
               ON (`at_description`.`entity_id` = `e`.`entity_id`) AND 
                  (`at_description`.`attribute_id` = (SELECT attribute_id FROM `eav_attribute` ea LEFT JOIN `eav_entity_type` et ON ea.entity_type_id = et.entity_type_id  WHERE `ea`.`attribute_code` = 'description' AND et.entity_type_code = 'catalog_product')) AND 
                  (`at_description`.`store_id` = 1) 

If you want it for an other store view, just replace the value 1 with your desired id at the following lines

(at_name.store_id = 1) and (at_description.store_id = 1)

I don't know why you need this in an sql format. This is a strange and a big error source. You can easily get it through code:

$collection = Mage::getResourceModel('catalog/product_collection')
        ->addAttributeToSelect(array('sku', 'name', 'description'));
foreach ($collection as $item) {
    $sku = $item->getSku();
    $name = $item->getName();
    $description = $item->getDescription(); 
    //do something with $sku, $name & $description
}
3
  • Can you add query for order list by sql Mar 27, 2021 at 5:27
  • @LitExtension Thanks for it. Can you also please tell me how to get "Quantity per Source" and "Sellable Quantity"?
    – Amit Saini
    Mar 27, 2021 at 14:39
  • I don't know why you need this query in sql format. This is NOT ULTIMATE and it is not recommended during magento development. Magento already supports query functions, you can use it instead of these sql commands. In your case, you can refer at: mageplaza.com/devdocs/… Mar 29, 2021 at 2:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.