Magento产品使用SQL查询从数据库导入 [英] Magento products import from database using SQL query

问题描述

Magento在其数据库系统中使用EAV结构.我有这个查询，可以给我magento商店中的product_id和产品名称.

Magento uses the EAV structure in its database system. I have this query that gives me product_id and name of products in my magento store.

SELECT e.entity_id AS product_id, var.value AS product_name
FROM catalog_product_entity e, eav_attribute eav, catalog_product_entity_varchar var
WHERE
   e.entity_type_id = eav.entity_type_id
   AND eav.attribute_code = 'name'
   AND eav.attribute_id = var.attribute_id
   AND var.entity_id = e.entity_id

我在获取product_url | price | image_url | description |制造商方面需要帮助

I need help in getting product_url|price|image_url|description|manufacturer

推荐答案

我不会发布整个SQL查询，因为试图通过数据库手动从Magento中获取数据太繁琐了，但是我会说您走在正确的轨道上.为了减少此类连接的数量，我从eav表中检索了attribute_ids并直接使用它们.这意味着我的查询仅适用于我的Magento安装，但这对我来说不是问题.

I'm not going to post the entire SQL query because it is far too tedious trying to get data out of Magento manually via the database, but I will say you're on the right track. To cut down on the number of joins for this sort of thing, I retrieve my attribute_ids from the eav table and use them directly. This means that my query will only work on my install of Magento, but that hasn't been an issue for me.

select attribute_code, attribute_id, backend_type from eav_attribute
    where entity_type_id = (select entity_type_id from eav_entity_type where entity_type_code = 'catalog_product')
      and attribute_code in ('name', 'url_path', 'price', 'image', 'description', 'manufacturer');

收益:

+----------------+--------------+--------------+
| attribute_code | attribute_id | backend_type |
+----------------+--------------+--------------+
| description    |           61 | text         |
| image          |           74 | varchar      |
| manufacturer   |           70 | int          |
| name           |           60 | varchar      |
| price          |           64 | decimal      |
| url_path       |           87 | varchar      |
+----------------+--------------+--------------+

现在您已经准备好沉闷了！对于每个属性代码，请与给定属性ID上的后端表(catalog_product_entity_$BACKEND_TYPE)联接.对我来说，这会将sku/name/id查询(您的查询实际上不需要针对产品进行连接，因为您使用entity_id进行连接...)变成:

Now you're ready for tedium! For each attribute code, join against the backend table (catalog_product_entity_$BACKEND_TYPE) on your given attribute ID. For me, this would turn a sku/name/id query (your query actually doesn't need to join against products, since you use the entity_id to make the join...) into:

select p.sku, p.entity_id, n.value name
    from catalog_product_entity p
    join catalog_product_entity_varchar n on n.entity_id = p.entity_id
  where n.attribute_id = 60;

继续添加新的连接语句| where-clause | select-clause集合，直到您拥有所有想要的连接为止.

Continue adding new join-statement|where-clause|select-clause sets until you have all the joins you wanted originally.

也就是说，乔纳森(Jonathan)是正确的，使用Magento框架管理这些数据要比通过数据库手动进行操作容易得多.除非您有大量的产品需要一次全部加载(请注意，这里有两个假设，并且您可以减少其中的两个假设)，否则使用该框架将更加健壮.

That said, Jonathan is correct that using the Magento framework to manage this data would be far easier than doing it manually via the database. Unless you have an extreme number of products that you need to load all at once (note there are two assumptions there, and you can work to reduce either), it would be far more robust to use the framework.

希望有帮助！

谢谢， 乔

这篇关于Magento产品使用SQL查询从数据库导入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！