Magento批量价格变动 [英] Magento bulk price changes

问题描述

我想做的是，某个类别产品的整体价格变化.我们从供应商那里收到建议的零售价格，但有时这些价格对我们不起作用.因此，我们需要考虑产品的成本价格，例如，将20％添加到产品中..足够容易的成本=成本+ 0.2 *成本.现在，我需要对所选类别中的所有产品执行此操作，因此，这就是我到目前为止所拥有的...

What I'm trying to do is, a bulk price change on products in a certain category. We receive suggested retail prices from our vendor, but sometimes these don't work for us. So we need to take the cost price of the product, and for example, add 20% to the product so.. easy enough cost = cost + 0.2*cost. Now I need to do this on all the products in the selected category, so here is what I have thus far...

$category = Mage::getModel('catalog/category')->load(189); 

// load products from category id '189'
$products   = Mage::getModel('catalog/product')
                ->getCollection()  
                ->addCategoryFilter($category)  
                ->addAttributeToSelect('id'); 

foreach($products as $product) { 
    // get the current cost of the product
    $cost = $db->fetchRow("SELECT value FROM `m_catalog_product_entity_decimal` WHERE entity_id='" . $product->getId() . "' AND attribute_id='68'");
    $cost = $cost['value']; 
    $newCost = $cost + $cost*$percentage; 

    // update the product with a new cost
    $db->query("UPDATE `m_catalog_product_entity_decimal` SET value='$newCost' WHERE entity_id='" . $product->getId() . "' AND attribute_id='64'"); 
}

现在，我需要使用原始SQL，因为我的php服务器无法处理所有magento产品的加载和保存(Magento 1.4在产品模型中存在内存泄漏).这就是为什么我只是从产品中选择"id"以获取最少数据量的原因.我也知道执行所有这些SQL查询会浪费资源，这就是为什么我在这里.如果我的每个类别都只说10个产品，那么我将使用产品模型来更新成本并保存产品，但是有时我一次在每个类别中最多拥有500个或更多产品.

Now, I need to use raw SQL because my php server can't handle all of the magento product loading and saving (Magento 1.4 has a memory leak in the product model). This is why I'm simply selecting the "id" from the product, to get the very least amount of data. I also understand that doing all of these SQL queries is a waste of resources, and thats why I'm here. If each of my categories only had say, 10 products, I would use the product model to update the cost and save the products, but I have sometimes up to 500 or more products in a each category as one time.

我希望将其压缩为一个SQL查询，并摆脱foreach循环和产品集合.成本属性id是68，价格属性id是64.对此的任何帮助将不胜感激.

I'm hoping to condense this to one SQL query, and get rid of the foreach loop and the product collection. The cost attribute id is 68, and the price attribute id is 64. Any help on this would be much appreciated.

编辑
Magento将EAV模型用于他们的数据库.因此，对于我需要访问的属性，即成本"和价格"，它们都位于m_catalog_product_entity_decimal

EDIT
Magento uses an EAV model for their database. So for the attributes I need to access, which are "cost" and "price", they are both located in m_catalog_product_entity_decimal

因此产品价格属性在表格中看起来像这样

So a products price attribute would look like this in the table

value_id    entity_type_id  attribute_id    store_id    entity_id   value
6401             4              64             0            2184      399.9500

value_id只是行的唯一值，entity_type_id 4表示这是产品属性. attribute_id与实际属性相关联.在这种情况下，"price"的attribute_id为64. Store_id无关紧要. Entity_id是实际产品ID，值是该商品的实际价格.

The value_id is just the rows unique value, entity_type_id 4 means this is a product attribute. The attribute_id is associated with the actual attribute. In this case, 64 is the attribute_id for "price". Store_id is irrelevant. Entity_id is the actual product id, and the value is the actual price of the item.

推荐答案

老实说，B00MER的想法正确，但之前我也不得不做类似的事情.另外，我想写一些SQL.如果您只想将某类别中所有产品的费用乘以1.2，则可以执行以下操作:

Honestly, B00MER has the right idea, but I've also had to do similar things in a pinch before. Plus, I felt like writing a little SQL. If you just want to multiply costs by 1.2 for all products in a category, you can do something like this:

update catalog_product_entity_decimal
  set value = value*1.2
  where attribute_id = 75 and
        entity_id IN (select product_id from catalog_category_product
                    where category_id = X);

毫无疑问，您将需要重新索引所有内容并检查结果以确保.显然，将X替换为您的目标类别，看来您使用的表前缀为m_，因此也要附加该前缀(我将其留给来这里的其他搜索者使用).

You'll doubtless need to reindex everything and check your results to make sure. Obviously, replace X with your target category, and it appears that you use a table prefix of m_, so append that too (I'm leaving it off for other searchers who come here).

希望有帮助！

谢谢， 乔

这篇关于Magento批量价格变动的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！