Change a backend attribute type from Multi-select to Dropdown
You not only have to change the backend_type in the eav_attribute table, but also copy the rows from the source to target table. Following code will help you accomplishing this:
$connection = _getConnection('core_write');
$attributeIds = 'yourattributeid';
$entityTypeId = 'oldentitytypeid;
$sql = 'SELECT * FROM catalog_product_entity_varchar WHERE attribute_id IN ('.$attributeIds.') AND entity_type_id = '.$entityTypeId;
$rows = $connection->fetchAll($sql);
foreach ($rows as $row){
$price = $row['value'];
if (!is_null($price)) {
$sql = 'INSERT INTO catalog_product_entity_decimal (`entity_type_id`,`attribute_id`,`store_id`,`entity_id`,`value`)
VALUES (?,?,?,?,?)';
$connection->query($sql, array($row['entity_type_id'], $row['attribute_id'], $row['store_id'], $row['entity_id'], $price));
}
$sql = 'DELETE FROM catalog_product_entity_varchar WHERE value_id = ?'; // or your old storage table
$connection->query($sql, $row['value_id']);
}
$sql = 'UPDATE eav_attribute SET backend_type="newbackendtype" WHERE attribute_id IN ('.$attributeIds.')';
$connection->query($sql);
This is loosely based on the answer by Tobias Zander, but is a bit more robust and cautious about the update so that it's safer to run on a production environment. It is also built explicitly to move a multiselect
to a select
, (dropdown.)
<?php
$installer = $this;
$installer->startSetup();
$setup = new Mage_Eav_Model_Entity_Setup('core_setup');
$attributeCode = 'my_atribute';
/** @var Mage_Catalog_Model_Resource_Eav_Attribute $attribute */
$attribute = Mage::getModel('catalog/resource_eav_attribute') ->loadByCode(Mage_Catalog_Model_Product::ENTITY, $attributeCode);
$attributeId = $attribute->getId();
$entityTypeId = $setup->getEntityTypeId('catalog_product');
$connection = $setup->getConnection('core_write');
$ea = $setup->getTable('eav_attribute');
$cea = $setup->getTable('catalog_eav_attribute');
$cpei = $setup->getTable('catalog_product_entity_int');
$cpev = $attribute->getBackendTable(); //$setup->getTable('catalog_product_entity_varchar');
$ip = $setup->getTable('index_process');
/*
* Sanity check on the existing attribute
*/
if ($attribute->getFrontendInput() != 'multiselect' || $attribute->getBackendType() != 'varchar') {
Mage::throwException("Cannot migrate `{$attributeCode}`` because it is not the expected backend or frontend type.");
return;
}
$connection->query("LOCK TABLES {$cpev} WRITE");
/*
* Check that none of the entities in the database have multiple selected values
*/
$num = (int)$connection->fetchOne("
SELECT COUNT(*) as num
FROM {$cpev}
WHERE ". $connection->prepareSqlCondition('attribute_id', $attributeId) ."
AND ". $connection->prepareSqlCondition('entity_type_id', $entityTypeId) ."
AND value LIKE '%,%'
");
if ($num !== 0) {
$connection->query("UNLOCK TABLES");
Mage::throwException("Cannot migrate `{$attributeCode}`` because it contains {$num} entities with multiple selected values.");
return;
}
$connection->beginTransaction();
$connection->query("
INSERT INTO {$cpei}
(`entity_type_id`, `attribute_id`, `store_id`, `entity_id`, `value`)
SELECT `entity_type_id`, `attribute_id`, `store_id`, `entity_id`, CONVERT(`value`,UNSIGNED INTEGER) as `value`
FROM {$cpev}
WHERE ". $connection->prepareSqlCondition('attribute_id', $attributeId) ."
AND ". $connection->prepareSqlCondition('entity_type_id', $entityTypeId) ."
AND value IS NOT NULL
AND value != ''
");
$connection->query("
UPDATE {$ea}
SET
`frontend_input` = 'select', # from 'multiselect'
`backend_type` = 'int', # from 'varchar'
`backend_model` = NULL, # from 'eav/entity_attribute_backend_array'
`source_model` = 'eav/entity_attribute_source_table' # from NULL
WHERE ". $connection->prepareSqlCondition('attribute_id', $attributeId) ."
");
/*
* OPTIONAL: Migrate the new attribute to be a configurable attribute
*/
$connection->query("
UPDATE {$cea}
SET `is_configurable` = '1'
WHERE ". $connection->prepareSqlCondition('attribute_id', $attributeId) ."
");
$connection->query("UNLOCK TABLES");
$connection->query("
DELETE
FROM {$cpev}
WHERE ". $connection->prepareSqlCondition('attribute_id', $attributeId) ."
AND ". $connection->prepareSqlCondition('entity_type_id', $entityTypeId) ."
");
$connection->query("
UPDATE {$ip}
SET `status` = 'require_reindex'
WHERE `indexer_code` IN ('catalog_product_attribute', 'catalog_product_flat')
");
$connection->commit();
$installer->endSetup();