4

I'm trying to delete unused dropdown options, the values that are not used by any product.

So what is the proper way to delete all unused dropdown options? What APIs should I use for this?

Here is a picture of where these options are managed in the backed, to help clarify which ones I'm talking about specifically.

Manage Options

6
  • Do you want to remove values from the catalog_product_entity_*** tables or eav_attribute_*** tables.? It would be good if you provide tables from which you want to remove values.
    – Softec
    Mar 13, 2020 at 17:51
  • I need to remove all the unused dropdown values, have no idea where or how, or even what they are called Mar 13, 2020 at 17:54
  • 1
    Wait, are they considered "Options"? Mar 13, 2020 at 17:58
  • How you will describe exactly as drop-downs? do you have backend screenshot, only for attribute type?
    – Softec
    Mar 13, 2020 at 18:00
  • Options or Options labels?
    – Softec
    Mar 13, 2020 at 18:01

2 Answers 2

2

I worked on a similar requirement(slightly different) and created a console command to achieve this. Here is the code according to your needs:

php bin/magento rlt:delete-attribute-options --attributeCode=color

di.xml

<?xml version="1.0" encoding="UTF-8"?>
    <config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:noNamespaceSchemaLocation="urn:magento:framework:ObjectManager/etc/config.xsd">
    <type name="Magento\Framework\Console\CommandList">
        <arguments>
            <argument name="commands" xsi:type="array">
                <item name="deleteProductOptions" xsi:type="object">
                    RLTSquare\DeleteUnusedOptions\Console\DeleteAttributeOptions
                </item>
            </argument>
        </arguments>
    </type>
</config>

DeleteAttributeOptions.php

<?php

namespace RLTSquare\DeleteUnusedOptions\Console;

use Magento\Catalog\Api\Data\ProductAttributeInterface;
use Magento\Eav\Model\ResourceModel\Entity\Attribute;
use Magento\Framework\App\ResourceConnection;
use Magento\Framework\App\State;
use Magento\Framework\Registry;
use Symfony\Component\Console\Command\Command;
use Symfony\Component\Console\Input\InputInterface;
use Symfony\Component\Console\Input\InputOption;
use Symfony\Component\Console\Output\OutputInterface;
use Symfony\Component\Console\Question\ConfirmationQuestion;

/**
 * Class DeleteAttributeOptions
 * @package RLTSquare\DeleteUnusedOptions\Console
 */
class DeleteAttributeOptions extends Command
{
    /**
     * @var State
     */
    private $state;
    /**
     * @var Registry
     */
    private $registry;
    /**
     * @var ResourceConnection
     */
    private $resourceConnection;
    /**
     * @var Attribute
     */
    private $eavAttribute;

    /**
     * DeleteAttributeOptions constructor.
     * @param State $state
     * @param ResourceConnection $resourceConnection
     * @param Attribute $eavAttribute
     * @param Registry $registry
     * @param string|null $name
     */
    public function __construct(
        State $state,
        ResourceConnection $resourceConnection,
        Attribute $eavAttribute,
        Registry $registry,
        ?string $name = null
    ) {
        $this->state = $state;
        parent::__construct($name);
        $this->registry = $registry;
        $this->resourceConnection = $resourceConnection;
        $this->eavAttribute = $eavAttribute;
    }

    protected function configure()
    {
        $this->setName('rlt:delete-attribute-options');
        $this->setDescription('Delete Unused attribute options.');

        $this->addOption(
            'attributeCode',
            null,
            InputOption::VALUE_REQUIRED,
            'Attribute code of attribute whose options you want to delete.'
        );

        parent::configure();
    }

    /**
     * @param InputInterface $input
     * @param OutputInterface $output
     * @return int|null|void
     * @throws \Exception
     */
    protected function execute(InputInterface $input, OutputInterface $output)
    {
        if (!$input->getOption('attributeCode')) {
            $output->writeln('Please specify attribute code in the command using --atributeCode argument.');
            return;
        }
        try {
            $this->state->setAreaCode(\Magento\Framework\App\Area::AREA_GLOBAL);
            $this->registry->register('isSecureArea', true);
        } catch (\Magento\Framework\Exception\LocalizedException $exception) {
        }
        $helper = $this->getHelper('question');
        $question = new ConfirmationQuestion(
            'Are you sure to delete attribute options? [y/N]',
            false
        );
        if ($helper->ask($input, $output, $question)) {
            $finalConfirmation = new ConfirmationQuestion(
                'This will delete unused attribute option for the attribute you specified. Are you 100% sure about it? [y/N]',
                false
            );
            if ($helper->ask($input, $output, $finalConfirmation)) {
                $this->deleteEntities($input, $output);
            } else {
                $output->writeln('Nothing deleted.');
            }
        } else {
            $output->writeln('Nothing deleted.');
        }
    }

    /**
     * @param string $code
     * @param OutputInterface $output
     * @return int
     */
    private function deleteAttributeOptions($code, $output)
    {
        $connection = $this->resourceConnection->getConnection();
        $attrId = $this->eavAttribute->getIdByCode(ProductAttributeInterface::ENTITY_TYPE_CODE, $code);

        //fetching all options related to specified attribute
        $eavTableName = $connection->getTableName('eav_attribute_option');
        $selectOne = $connection->select()->from(
            ['eavAtt' => $eavTableName],
            ['option_id']
        )->where('attribute_id IN(?)', $attrId);
        $options = $selectOne->query()->fetchAll();
        $options = array_column($options, 'option_id');

        //fetching options that are assigned to product for that attribute
        $prodTableName = $connection->getTableName('catalog_product_entity_int');
        $selectTwo = $connection->select()->from(
            ['catPrd' => $prodTableName],
            ['value']
        )->where('attribute_id IN(?)', $attrId);
        $usedOptions = $selectTwo->query()->fetchAll();
        $usedOptions = array_column($usedOptions, 'value');

        //difference of both to get unused option ids which will be used for delete operation
        $notUsedOptions = array_diff($options, $usedOptions);
        $whereForDelete = ['option_id IN(?)' => $notUsedOptions];

        $output->writeln(
            'Found ' . count($notUsedOptions) . ' unused options for attribute code => ' . $code
        );
        return $connection->delete($eavTableName, $whereForDelete);
    }

    /**
     * @param InputInterface $input
     * @param OutputInterface $output
     * @throws \Exception
     */
    protected function deleteEntities(InputInterface $input, OutputInterface $output): void
    {
        $attrCode = $input->getOption('attributeCode');

        $output->writeln('Deleting Unused Attribute options.');
        $this->deleteAttributeOptions($attrCode, $output);
        $output->writeln('Deleted unused attribute options.');
    }
}

Remember to take database backup just to be on safe side.

Here is the bitbucket repo link for module.

1

You could try running this query in your database

SELECT
  eao.attribute_id,
  ea.attribute_code,
  eaov.value_id,
  eaov.value
FROM eav_attribute_option AS eao
  INNER JOIN eav_attribute AS ea ON eao.attribute_id = ea.attribute_id
  INNER JOIN eav_attribute_option_value AS eaov ON eaov.option_id = eao.option_id
WHERE eao.option_id NOT IN (
  SELECT value
  FROM catalog_product_entity_int
  WHERE attribute_id = eao.attribute_id
) AND  ea.attribute_code = 'your_attribute_code';

It will lists options of **your_attribute_code** is not used by any product

Review the list option before deleting them

I hope it works well for you

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.