MAGENTO 2: CHANGING PRODUCT ATTRIBUTE TYPE

In Magento 2, once we create an attribute type, we cannot change it by configuring the admin, but it is possible to change the attribute type programmatically.

For example, let us assume the following – an attribute called “size”, the input type of that attribute as “Text” and approximately 3000 products in the store. It is also assumed that we have already entered various attribute values like ‘small’, ‘medium’ and ‘large’, for, say, 1000 products.

At a later point in time, we begin to feel that the current ‘text’ attribute type is cumbersome and time-consuming. We would, instead, prefer to have a ‘dropdown’ attribute type. This drop down will be used to choose the values ‘S’ or ‘M’ or ‘L’ instead of typing the same in the ‘text’ attribute type.

Magento 2: Changing Product Attribute Type

Admin does not allow you to change the attribute type once it is created and values have been entered. But as mentioned above we can achieve this programmatically.We have to do this without losing the existing attribute values that (in the above example) have been entered for 1000 products.

When the attribute is in “Text”, the product value of this attribute is stored in the “catalog_product_entity_varchar”, but when we change the attribute type as “Dropdown”, the product value will be stored in “catalog_product_entity_int”.

By using the below code we can change the product attribute type from text box to drop down in a short time without losing the existing values.

Place this code in the root directory of Magento 2 files and execute it.

<?php
error_reporting(1);
ini_set('max_execution_time', 0);
 
use \Magento\Framework\App\Bootstrap;
require __DIR__ . '/app/bootstrap.php';
$bootstrap = Bootstrap::create(BP, $_SERVER);
$objectManager = $bootstrap->getObjectManager();
$url = \Magento\Framework\App\ObjectManager::getInstance();
$storeManager = $url->get('\Magento\Store\Model\StoreManagerInterface');
$mediaurl= $storeManager->getStore()->getBaseUrl(\Magento\Framework\UrlInterface::URL_TYPE_MEDIA);
$state = $objectManager->get('\Magento\Framework\App\State');
$state->setAreaCode('frontend');
 
// Attribute Id that we need to change
$attributeId = 162;  
 
$resource = $objectManager->get('Magento\Framework\App\ResourceConnection');
$connection = $resource->getConnection();
 
// Get the product values that are already stored in the database for given attribute
$catalog_product_entity_varchar = $resource->getTableName('catalog_product_entity_varchar');
$attribute_values = $connection->fetchAll("SELECT DISTINCT attribute_id, value FROM $catalog_product_entity_varchar where attribute_id = $attributeId");
 
if(!empty($attribute_values)) {
 
          foreach ($attribute_values as $_attribute_values) {
 
                     $attribute_values = $connection->fetchRow("SELECT count(*) as cnt FROM `eav_attribute_option` where attribute_id = $attributeId;");
                     $count = $attribute_values['cnt'] + 1;
                     // Insert the product values as an option for a given attribute
                    $eav_attribute_option = $resource->getTableName('eav_attribute_option');
                    $sql = "insert into $eav_attribute_option(option_id, attribute_id, sort_order) values (null, $attributeId, $count)";
                     try {
                               $resp = $connection->query($sql);    
                    } catch (Exception $e) {
                              echo '<pre>';  print_r($e->getMessage());
                    }
     
                    $lastInsertId = $connection->lastInsertId();
 
                    $eav_attribute_option_value = $resource->getTableName('eav_attribute_option_value');
                    $sql = "insert into $eav_attribute_option_value(value_id, option_id, store_id, value) values (null, $lastInsertId, 0, '$_attribute_values[value]')";
                    try {
                              $resp = $connection->query($sql);    
                    } catch (Exception $e) {
                              echo '<pre>';  print_r($e->getMessage());
                    }
 
                    $sql = "insert into $eav_attribute_option_value(value_id, option_id, store_id, value) values (null, $lastInsertId, 1, '$_attribute_values[value]')";
                    try {
                              $resp = $connection->query($sql);    
                    } catch (Exception $e) {
                              echo '<pre>';  print_r($e->getMessage());
                    }
          }
}
 
$catalog_product_entity_varchar = $resource->getTableName('catalog_product_entity_varchar');
$attribute_values = $connection->fetchAll("SELECT * FROM $catalog_product_entity_varchar where attribute_id = $attributeId");
 
if(!empty($attribute_values)) {
          foreach ($attribute_values as $_attribute_values) {
 
                    // Get the option id for the specific product
                    $option_values = $connection->fetchRow("SELECT * FROM `eav_attribute_option` as eao INNER JOIN `eav_attribute_option_value` as eaov on eao.option_id = eaov.option_id WHERE eao.attribute_id = $attributeId and eaov.store_id = 1 and eaov.value = '$_attribute_values[value]'");
     
                    if(!empty($option_values)) {
                              $catalog_product_entity_int = $resource->getTableName('catalog_product_entity_int');
                              $product_values_exist = $connection->fetchRow("SELECT value_id FROM $catalog_product_entity_int WHERE attribute_id = $attributeId and entity_id = $_attribute_values[entity_id]");
 
                              if(empty($product_values_exist)) {
                                        $sql = "insert into $catalog_product_entity_int(value_id, attribute_id, store_id, entity_id, value) values (null, $attributeId, 0, $_attribute_values[entity_id], $option_values[option_id])";
                                        try {
                                                 $resp = $connection->query($sql);    
                                        } catch (Exception $e) {
                                                  echo '<pre>';  print_r($e->getMessage());
                                        }
 
                              } else {
                                        $sql = "Update $catalog_product_entity_int set value = $option_values[option_id] WHERE attribute_id = $attributeId and entity_id = $_attribute_values[entity_id]";
                                        try {
                                                  $resp = $connection->query($sql);    
                                        } catch (Exception $e) {
                                                  echo '<pre>';  print_r($e->getMessage());
                                        }
                              }
                    }
          }
}
 
// Change the attribute type to dropdown
$eav_attribute = $resource->getTableName('eav_attribute');
$sql = "UPDATE $eav_attribute SET `backend_type` = 'int', `frontend_input` = 'select', `source_model` = 'Magento\\\Eav\\\Model\\\Entity\\\Attribute\\\Source\\\Table' WHERE `attribute_id` = $attributeId";
try {
          $resp = $connection->query($sql);
} catch (Exception $e) {
          echo '<pre>';  print_r($e->getMessage());
}
 
$catalog_eav_attribute = $resource->getTableName('catalog_eav_attribute');
$sql = "UPDATE $catalog_eav_attribute SET `is_filterable` = 1, is_comparable = 1, is_visible_on_front = 1, is_html_allowed_on_front = 1, is_filterable_in_search = 1, used_in_product_listing = 1 WHERE `attribute_id` = $attributeId";
try {
          $resp = $connection->query($sql);
} catch (Exception $e) {
          echo '<pre>';  print_r($e->getMessage());
}

100

101

102

103

104

105

106

107

<?php

error_reporting(1);

ini_set('max_execution_time', 0);

use \Magento\Framework\App\Bootstrap;

require __DIR__ . '/app/bootstrap.php';

$bootstrap = Bootstrap::create(BP, $_SERVER);

$objectManager = $bootstrap->getObjectManager();

$url = \Magento\Framework\App\ObjectManager::getInstance();

$storeManager = $url->get('\Magento\Store\Model\StoreManagerInterface');

$mediaurl= $storeManager->getStore()->getBaseUrl(\Magento\Framework\UrlInterface::URL_TYPE_MEDIA);

$state = $objectManager->get('\Magento\Framework\App\State');

$state->setAreaCode('frontend');

// Attribute Id that we need to change

$attributeId = 162;

$resource = $objectManager->get('Magento\Framework\App\ResourceConnection');

$connection = $resource->getConnection();

// Get the product values that are already stored in the database for given attribute

$catalog_product_entity_varchar = $resource->getTableName('catalog_product_entity_varchar');

$attribute_values = $connection->fetchAll("SELECT DISTINCT attribute_id, value FROM $catalog_product_entity_varchar where attribute_id = $attributeId");

if(!empty($attribute_values)) {

foreach ($attribute_values as $_attribute_values) {

$attribute_values = $connection->fetchRow("SELECT count(*) as cnt FROM `eav_attribute_option` where attribute_id = $attributeId;");

$count = $attribute_values['cnt'] + 1;

// Insert the product values as an option for a given attribute

$eav_attribute_option = $resource->getTableName('eav_attribute_option');

$sql = "insert into $eav_attribute_option(option_id, attribute_id, sort_order) values (null, $attributeId, $count)";

try {

$resp = $connection->query($sql);

} catch (Exception $e) {

echo '<pre>'; print_r($e->getMessage());

}

$lastInsertId = $connection->lastInsertId();

$eav_attribute_option_value = $resource->getTableName('eav_attribute_option_value');

$sql = "insert into $eav_attribute_option_value(value_id, option_id, store_id, value) values (null, $lastInsertId, 0, '$_attribute_values[value]')";

try {

$resp = $connection->query($sql);

} catch (Exception $e) {

echo '<pre>'; print_r($e->getMessage());

}

$sql = "insert into $eav_attribute_option_value(value_id, option_id, store_id, value) values (null, $lastInsertId, 1, '$_attribute_values[value]')";

try {

$resp = $connection->query($sql);

} catch (Exception $e) {

echo '<pre>'; print_r($e->getMessage());

}

$catalog_product_entity_varchar = $resource->getTableName('catalog_product_entity_varchar');

$attribute_values = $connection->fetchAll("SELECT * FROM $catalog_product_entity_varchar where attribute_id = $attributeId");

if(!empty($attribute_values)) {

foreach ($attribute_values as $_attribute_values) {

// Get the option id for the specific product

$option_values = $connection->fetchRow("SELECT * FROM `eav_attribute_option` as eao INNER JOIN `eav_attribute_option_value` as eaov on eao.option_id = eaov.option_id WHERE eao.attribute_id = $attributeId and eaov.store_id = 1 and eaov.value = '$_attribute_values[value]'");

if(!empty($option_values)) {

$catalog_product_entity_int = $resource->getTableName('catalog_product_entity_int');

$product_values_exist = $connection->fetchRow("SELECT value_id FROM $catalog_product_entity_int WHERE attribute_id = $attributeId and entity_id = $_attribute_values[entity_id]");

if(empty($product_values_exist)) {

$sql = "insert into $catalog_product_entity_int(value_id, attribute_id, store_id, entity_id, value) values (null, $attributeId, 0, $_attribute_values[entity_id], $option_values[option_id])";

try {

$resp = $connection->query($sql);

} catch (Exception $e) {

echo '<pre>'; print_r($e->getMessage());

}

} else {

$sql = "Update $catalog_product_entity_int set value = $option_values[option_id] WHERE attribute_id = $attributeId and entity_id = $_attribute_values[entity_id]";

try {

$resp = $connection->query($sql);

} catch (Exception $e) {

echo '<pre>'; print_r($e->getMessage());

}

// Change the attribute type to dropdown

$eav_attribute = $resource->getTableName('eav_attribute');

$sql = "UPDATE $eav_attribute SET `backend_type` = 'int', `frontend_input` = 'select', `source_model` = 'Magento\\\Eav\\\Model\\\Entity\\\Attribute\\\Source\\\Table' WHERE `attribute_id` = $attributeId";

try {

$resp = $connection->query($sql);

} catch (Exception $e) {

echo '<pre>'; print_r($e->getMessage());

}

$catalog_eav_attribute = $resource->getTableName('catalog_eav_attribute');

$sql = "UPDATE $catalog_eav_attribute SET `is_filterable` = 1, is_comparable = 1, is_visible_on_front = 1, is_html_allowed_on_front = 1, is_filterable_in_search = 1, used_in_product_listing = 1 WHERE `attribute_id` = $attributeId";

try {

$resp = $connection->query($sql);

} catch (Exception $e) {

echo '<pre>'; print_r($e->getMessage());

}