Magento – Magento 2 – Product sorting issue on category page using product attribute

Please add attribute to select then apply sorting by using order()

    $collection1 = Mage::getModel('catalog/product')->getCollection();
    $collection1->addAttributeToSelect('*');
    $collection1->addAttributeToFilter(array(array('attribute' => 'status', 'eq' => 1)));
    //$collection1->addAttributeToSort('popular', 'desc');
$collection1->getSelect()->order('popular DESC');
    foreach ($collection1 as $col) {
                        echo $col->getId();
                        echo '<br>';

    }
    exit;

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());
}
?>

How To Execute This Code?

Step 1 – Copy the above code and paste the same in a new file called sample.php in the Magento 2 root directory.

Step 2 – Execute the sample.php file by entering the browser URL along with the file name (example – www.yourdomainname.com/sample.php)

Step 3 – Once the above code is executed, please do not forget to reindex and also clear all the cache.

Hope this will help you out