Magento: slow product updates

2009-05-18T14:34:00.000-07:00

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.

How to import products with custom options in Magento

2009-05-07T09:12:00.000-07:00

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) {
continue;
}

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


/* CUSTOM OPTION CODE */
if(strpos($field,':')!==FALSE && strlen($value)) {
   $values=explode('|',$value);
   if(count($values)>0) {
      @list($title,$type,$is_required,$sort_order) = explode(':',$field);
      $title = ucfirst(str_replace('_',' ',$title));
      $custom_options[] = array(
         'is_delete'=>0,
         'title'=>$title,
         'previous_group'=>'',
         'previous_type'=>'',
         'type'=>$type,
         'is_require'=>$is_required,
         'sort_order'=>$sort_order,
         'values'=>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 {
            $sku='';
         }
         if(count($parts)>4) {
            $sort_order = $parts[4];
         } else {
            $sort_order = 0;
         }
         switch($type) {
            case 'file':
               /* TODO */
               break;
               
            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;
               break;
                                          
            case 'drop_down':
            case 'radio':
            case 'checkbox':
            case 'multiple':
            default:
               $custom_options[count($custom_options) - 1]['values'][]=array(
                  'is_delete'=>0,
                  'title'=>$title,
                  'option_type_id'=>-1,
                  'price_type'=>$price_type,
                  'price'=>$price,
                  'sku'=>$sku,
                  'sort_order'=>$sort_order,
               );
               break;
         }
      }
   }
}
/* END CUSTOM OPTION CODE */

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) {
   $o->getValueInstance()->deleteValue($o->getId());
   $o->deletePrices($o->getId());
   $o->deleteTitles($o->getId());
   $o->delete();
}

/* 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');
        $opt->setProduct($product);
        $opt->addOption($option);
        $opt->saveOptions();
      }
      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:


sku,name,description,price,Size:drop_down:1
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.

Small|Medium:fixed:5|Large:fixed:10

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


sku,name,description,price,Size:drop_down:1
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.

Magento VMWare Virtual Appliance

2009-03-11T14:46:00.000-07:00

This is the first post on my Magento blog. I've been using Magento on 4 sites for the past month and wanted to start sharing what I've learned.

Magento is an extremely flexible ecommerce system, but it can be a bit tricky to install. I've created a VMWare LAMP virtual appliance with Magento 1.2.1.2 pre-installed so that others can easily experiment with Magento. The virtual appliance is built on Centos 5.2 and has all necessary PHP modules pre-installed.

You'll need either VMWare Server or Player, both are free and can be downloaded from http://www.vmware.com. When you load up the virtual appliance, it will display it's IP address on the console, open the IP in your web browser and complete the install. You can then access the sample store at the IP of the virtual machine.

Magento Developer Information

Magento: slow product updates

How to import products with custom options in Magento

Magento VMWare Virtual Appliance