如何仅使用 mysql 在 magento 中提取所有产品 id、skus、产品名称、描述?

2021-12-19 00:00:00 magento mysql

我将如何使用 Magento 数据库中的 mysql 提取所有产品 ir、skus、产品名称(标题)和 desxription?我使用了以下查询并获得了除产品名称之外的所有属性.

How i will pull all the prduct ir, skus , product name (titles) and desxription using mysql from Magento database? I used following query and got all the attributes except product names.

SELECT e.entity_id, e.sku, eav.value AS 'description'
FROM catalog_product_entity e
JOIN catalog_product_entity_text eav
  ON e.entity_id = eav.entity_id
JOIN eav_attribute ea
  ON eav.attribute_id = ea.attribute_id
WHERE ea.attribute_code = 'description'

推荐答案

标题可以从一个商店视图到另一个不同.描述也是如此.此外，某些商店视图可以使用在后端设置的默认值.

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.

这是关于如何为特定商店视图 (id 1) 的所有产品获取所需数据(sku、名称、描述)的完整查询.

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)

如果您希望将其用于其他商店视图，只需在以下几行将值 1 替换为您想要的 id

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)

和 

(`at_description`.`store_id` = 1)

我不知道你为什么需要 sql 格式的这个.这是一个奇怪而大的错误源.可以通过代码轻松搞定:

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
}

相关文章