Magento:2 How to save unique values for my custom admin module

Question

I am making a custom admin module which store custom data. I want to save unique values in table field.

How I can check unique column value before save data, show error message if values already exist.

Which is the best way as per Magento standard?

Thank

Answer 1

I think you should follow the same path which Magento follow in module CMS for unique cms block identifier and unique cms page url, you can add _beforeSave method in your ResourceModel class and check your unique value.

In CMS block you can find this,

 protected function _beforeSave(AbstractModel $object)
    {
        if (!$this->getIsUniqueBlockToStores($object)) {
            throw new LocalizedException(
                __('A block identifier with the same properties already exists in the selected store.')
            );
        }
        return $this;
    }

File path vendor\magento\module-cms\Model\ResourceModel\Block.php

From here you can get your starting point

Answer 2

I have created one slider module in which there is slide table linked with store tables. So I have any of slides does not have the same position based on store. For this, you need to add two tables, one is main table and the other is store table. Here in this case, there are tables slide and slide_store tables.

Check the below code to add tables.

<?php

namespace [Package_Name][Module_Name]\Setup;

use Magento\Framework\Setup\InstallSchemaInterface;
use Magento\Framework\Setup\ModuleContextInterface;
use Magento\Framework\Setup\SchemaSetupInterface;
use Magento\Framework\DB\Adapter\AdapterInterface;

/**
 * @codeCoverageIgnore
 */
class InstallSchema implements InstallSchemaInterface
{
    /**
     * {@inheritdoc}
     * @SuppressWarnings(PHPMD.ExcessiveMethodLength)
     */
    public function install(SchemaSetupInterface $setup, ModuleContextInterface $context)
    {
        $installer = $setup;

        $installer->startSetup();

        /**
         * Create table 'slide'
         */
        $table = $installer->getConnection()->newTable(
            $installer->getTable('slide')
        )->addColumn(
            'entity_id',
            \Magento\Framework\DB\Ddl\Table::TYPE_SMALLINT,
            null,
            ['identity' => true, 'nullable' => false, 'primary' => true],
            'Block ID'
        )->addColumn(
            'title',
            \Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
            255,
            ['nullable' => false],
            'Block Title'
        )->addColumn(
            'position',
            \Magento\Framework\DB\Ddl\Table::TYPE_INTEGER,
            null,
            ['nullable' => true, 'default' => '0'],
            'Position'
        )->addColumn(
            'is_active',
            \Magento\Framework\DB\Ddl\Table::TYPE_SMALLINT,
            null,
            ['nullable' => false, 'default' => '1'],
            'Is Block Active'
        )->setComment(
            'Slide Table'
        );
        $installer->getConnection()->createTable($table);

        /**
         * Create table 'slide_store'
         */
        $table = $installer->getConnection()->newTable(
            $installer->getTable('slide_store')
        )->addColumn(
            'entity_id',
            \Magento\Framework\DB\Ddl\Table::TYPE_SMALLINT,
            null,
            ['nullable' => false, 'primary' => true],
            'Block ID'
        )->addColumn(
            'store_id',
            \Magento\Framework\DB\Ddl\Table::TYPE_SMALLINT,
            null,
            ['unsigned' => true, 'nullable' => false, 'primary' => true],
            'Store ID'
        )->addIndex(
            $installer->getIdxName('slide_store', ['store_id']),
            ['store_id']
        )->addForeignKey(
            $installer->getFkName('slide_store', 'entity_id', 'slide', 'entity_id'),
            'entity_id',
            $installer->getTable('slide'),
            'entity_id',
            \Magento\Framework\DB\Ddl\Table::ACTION_CASCADE
        )->addForeignKey(
            $installer->getFkName('slide_store', 'store_id', 'store', 'store_id'),
            'store_id',
            $installer->getTable('store'),
            'store_id',
            \Magento\Framework\DB\Ddl\Table::ACTION_CASCADE
        )->setComment(
            'Slide To Store Linkage Table'
        );
        $installer->getConnection()->createTable($table);

        $installer->endSetup();
    }
}

To use above table, you need to create three model files.

i.e.,

[Package_Name][Module_Name]\Model\Slide.php

[Package_Name][Module_Name]\Model\ResourceModel\Slide.php

[Package_Name][Module_Name]\Model\ResourceModel\Slide\Collection.php

Finally, to check the unique position based on store, you need to write _beforeSave() of [Package_Name][Module_Name]\Model\ResourceModel\Slide.php.

<?php

namespace [Package_Name][Module_Name]\Model\ResourceModel;

use Magento\Framework\Model\ResourceModel\Db\AbstractDb;
use Magento\Store\Model\Store;
use Magento\Framework\Model\AbstractModel;
use Magento\Framework\Exception\LocalizedException;

class Slide extends AbstractDb
{
    protected function _construct()
    {
        $this->_init('slide', 'entity_id');
    }

    protected function _beforeSave(AbstractModel $object)
    {
        if (!$this->checkIsUniquePosition($object)) {
            throw new LocalizedException(
                __('Position needs to be unique for each slide.')
            );
        }

        return parent::_beforeSave($object);
    }

    public function checkIsUniquePosition(AbstractModel $object)
    {
        if ($this->_storeManager->hasSingleStore()) {
            $stores = [Store::DEFAULT_STORE_ID];
        } else {
            $stores = (array)$object->getData('stores');
        }

        $select = $this->getConnection()->select()
            ->from(['slide' => $this->getMainTable()])
            ->join(
                ['slide_store' => $this->getTable('slide_store')],
                'slide.entity_id = slide_store.entity_id',
                []
            )
            ->where('slide.position = ?', $object->getData('position'))
            ->where('slide_store.store_id IN (?)', $stores);

        if ($object->getId()) {
            $select->where('slide.entity_id <> ?', $object->getId());
        }

        if ($this->getConnection()->fetchRow($select)) {
            return false;
        }

        return true;
    }
}