How to Use of insertMultiple mysql query in Magento 2

Sunday, September 1, 2024

insertMultiple query is very useful for inserting multiple rows/records simultaneously.

It helps us enhance performance and expedite the process.

We'll employ a more efficient method instead of relying on the conventional factory with a foreach loop. Let’s examine the code below for more details.

Base Definition of function:

Magento\Framework\DB\Adapter\AdapterInterface::insertMultiple($table, array $data);

/**
 * Inserts a table multiply rows with specified data.
 *
 * @param mixed $table The table to insert data into.
 * @param array $data Column-value pairs or array of Column-value pairs.
 * @return int The number of affected rows.
 */
public function insertMultiple($table, array $data);

Let’s check how we can use this insertMultiple() query operation in our custom module.

Check the piece of code below.

Where we have used insertMultiple() to insert the multiple records data.

It will return the number of affected/inserted rows.

<?php
/**
 * Copyright ©  All rights reserved.
 * See COPYING.txt for license details.
 */
declare(strict_types=1);
 
namespace Magelearn\Categoryfaq\Model\ResourceModel;
 
use Magento\Backend\Model\Auth;
use Magento\Framework\App\RequestInterface;
use Magento\Framework\Event\ManagerInterface;
use Magento\Framework\Model\ResourceModel\Db\AbstractDb;
use Magento\Framework\Model\AbstractModel;
use Magento\Framework\Model\ResourceModel\Db\Context;
use Magento\Framework\Stdlib\DateTime\DateTime;
use Magelearn\Categoryfaq\Model\Question as QuestionModel;
 
class Question extends AbstractDb
{
    /**
     * @var string
     */
    protected $_idFieldName = 'question_id';
    /**
     * Date model
     *
     * @var DateTime
     */
    public $date;
     
    /**
     * Event Manager
     *
     * @var ManagerInterface
     */
    public $eventManager;
     
    /**
     * Question Category relation model
     *
     * @var string
     */
    public $questionCategoryTable;
     
    /**
     * @var Auth
     */
    protected $_auth;
     
    /**
     * @var RequestInterface
     */
    protected $_request;
 
     
    /**
     * Question constructor.
     *
     * @param Context $context
     * @param DateTime $date
     * @param ManagerInterface $eventManager
     * @param Auth $auth
     * @param RequestInterface $request
     */
    public function __construct(
        Context $context,
        DateTime $date,
        ManagerInterface $eventManager,
        Auth $auth,
        RequestInterface $request
        ) {
            $this->date           = $date;
            $this->eventManager   = $eventManager;
            $this->_auth          = $auth;
            $this->_request       = $request;
            parent::__construct($context);
 
            $this->questionCategoryTable = $this->getTable('magelearn_categoryfaq_question_category');
    }
 
    /**
     * @inheritDoc
     */
    protected function _construct()
    {
        $this->_init('magelearn_categoryfaq_question', 'question_id');
    }
     
    /**
     * @param QuestionModel|AbstractModel $object
     * @return AbstractDb
     * @throws LocalizedException
     */
    protected function _afterSave(AbstractModel $object)
    {
        $this->saveCategoryRelation($object);
         
        return parent::_afterSave($object);
    }
     
    /**
     * @param PostModel $post
     *
     * @return $this
     * @throws LocalizedException
     */
    public function saveCategoryRelation(QuestionModel $question)
    {
        $question->setIsChangedCategoryList(false);
        $id             = $question->getId();
        $categories     = $question->getCategoriesIds();
        $oldCategoryIds = $question->getCategoryIds();
         
        if ($categories === null) {
            return $this;
        }
         
        $insert         = array_diff($categories, $oldCategoryIds);
        $delete         = array_diff($oldCategoryIds, $categories);
        $adapter        = $this->getConnection();
         
        if (!empty($delete)) {
            $condition = ['category_id IN(?)' => $delete, 'question_id=?' => $id];
            $adapter->delete($this->questionCategoryTable, $condition);
        }
        if (!empty($insert)) {
            $data = [];
            foreach ($insert as $categoryId) {
                $data[] = [
                    'question_id'     => (int) $id,
                    'category_id' => (int) $categoryId,
                    'position'    => 1
                ];
            }
            $adapter->insertMultiple($this->questionCategoryTable, $data);
        }
        
        .....
         
        return $this;
    }
}

Tag : Magento2

Magento 𝟮