Monday, May 18, 2009

Magento: slow product updates

Ever since I upgraded to 1.3.1 simply saving a product takes a very long time (2-3 minutes). This was driving me (and others updating products) crazy.

I finally resolved the issue. I was reviewing the MySQL processlist while magento was updating a product and noticed a very long running thread:

invalidating query cache entries (key) DELETE FROM `catalogindex_aggregation` WHERE (aggregation_id IN (SELECT `catalogindex_aggregation_to
I had enabled query caching per this post on the magento groups. When I disabled query caching in MySQL the slow product updates issue disappeared. Now saving a product only takes 2-3 seconds.

Thursday, May 7, 2009

How to import products with custom options in Magento

Over the past several months I've been using Magento on four stores. I've run into a few things which have required slight modifications.

Recently I needed to import 200 new products. The products all had between 3 and 4 options. The default Magento import does not allow you to import custom options for products so I customized the import to allow imporing of options.

To add this customization to your magento store first copy app/code/core/Mage/Catalog/Model/Convert/Adapter/Product.php to app/code/local/Mage/Catalog/Model/Convert/Adapter/Product.php. This will prevent upgrades from overwriting your changes.

Next you need to add some code to "local" version of Product.php (code/local/Mage/Catalog/Model/Convert/Adapter/Product.php). The line numbers below correspond to version 1.3 they may be a bit different in older versions.

At about line 566 you will see:

foreach ($importData as $field => $value) {

Just above that add:

$custom_options = array();

At about line 575 you will see:

$attribute = $this->getAttribute($field);
if (!$attribute) {

You will need to add some code above the continue statement.

if(strpos($field,':')!==FALSE && strlen($value)) {
if(count($values)>0) {
@list($title,$type,$is_required,$sort_order) = explode(':',$field);
$title = ucfirst(str_replace('_',' ',$title));
$custom_options[] = array(
foreach($values as $v) {
$parts = explode(':',$v);
$title = $parts[0];
if(count($parts)>1) {
$price_type = $parts[1];
} else {
$price_type = 'fixed';
if(count($parts)>2) {
$price = $parts[2];
} else {
$price =0;
if(count($parts)>3) {
$sku = $parts[3];
} else {
if(count($parts)>4) {
$sort_order = $parts[4];
} else {
$sort_order = 0;
switch($type) {
case 'file':
/* TODO */

case 'field':
case 'area':
$custom_options[count($custom_options) - 1]['max_characters'] = $sort_order;
/* NO BREAK */

case 'date':
case 'date_time':
case 'time':
$custom_options[count($custom_options) - 1]['price_type'] = $price_type;
$custom_options[count($custom_options) - 1]['price'] = $price;
$custom_options[count($custom_options) - 1]['sku'] = $sku;

case 'drop_down':
case 'radio':
case 'checkbox':
case 'multiple':
$custom_options[count($custom_options) - 1]['values'][]=array(

Now further on in the code (about line 710) you'll see $product->save(); Just after this add the following code:

/* Remove existing custom options attached to the product */
foreach ($product->getOptions() as $o) {

/* Add the custom options specified in the CSV import file */
if(count($custom_options)) {
foreach($custom_options as $option) {
try {
$opt = Mage::getModel('catalog/product_option');
catch (Exception $e) {}

That's it, you should be all set to import custom product options.

To import a custom option you need to add a new column to your CSV import file. The name of the column determines the name and type of the option. The format is: Name:Type:Is Required. For example, to create a required drop down option called "Size" your column header should be: Size:drop_down:1 (1 for required, 0 for optional). Here is a list of the Types, these are taken from the "Custom Options" screen in the Magento admin area.
  • field: Field
  • area: Area
  • file: File
  • drop_down: Drop-down
  • radio: Radio Buttons
  • checkbox: Checkbox
  • multiple: Multiple Select
  • date: Date
  • date_time: Date & Time
  • time: Time
For types with multiple values (drop_down, radio, checkbox, multiple) you can specify using a | separator. Example for Small, Medium, Large you would use "Small|Medium|Large" as the value for the "Size:drop_down:1" column in your csv file.

Here's paired down example of the import format:

T-Shirt1,T-Shirt,A T-Shirt,5.00,Small|Medium|Large
T-Shirt2,T-Shirt2,Another T-Shirt,6.00,XS|S|M|L|XL

In addition you can specify an addition price and SKU modifier for each option value. The syntax for this is Value:[fixed|percent]:price_modifier. For example if you have a product which costs $5 more for a Medium and $10 more for a large you would the following as the option value.


Here's the first example with additional price/sku modifiers.

T-Shirt1,T-Shirt,A T-Shirt,5.00,Small:fixed:0:-SM|Medium:percent:2:-MED|Large:percent:3:-LRG
T-Shirt2,T-Shirt2,Another T-Shirt,6.00,XS:fixed:0:-XS|S:fixed:0:-S|M:fixed:1:-M|L:fixed:1:-L|XL:fixed:2:-XL

UPDATE: 5/12
Here's a sample CSV file I used to test code additions by @gancheff and @Brendan.

I hope this modification helps others who need to import product options.