How to add/Update multiple records to database in Magento 2

While im going to discuss with you in later part of this blog on how  to add / upgrade multiple records to the database in Magento 2, I would like you to have a glimpse of our Magento services here, if you have a hard time in performing such activity.

Here In my example, I will be adding a new table in MySQL: my_custom_table with two fields:

Id: Main key, Item ID

qty: int (11), number of item

In Magento 2, almost all the records have some changes in the database. To add / update multiple records for the database, the process goes little different from the first edition of Magento. Of course,  here is how we do that.

How to add/Update multiple records to database in Magento 2

  • You can usually update multiple records in the database using a save () method of model.
$updateData = [1 => 5, 2 =>12, 3 => 16, 4=>5];

foreach($collection as $item) {

    if(isset($updateData[$item->getId()])) {

        $item->setData('qty', $updateData[$item->getId()];

        $item->save();

    }

}

However, this is not recommended because its performance is not good. You can see the save () function is called a loop. I got another solution to solve this problem.

In my resource sample I have written the following command: /app/code/[NameSpace]/[ModuleName]/Model/ResourceModel/Custom.php

$updateData = [1 => 5, 2 =>12, 3 => 16, 4=>5];

$connection = $this->getConnection();

$conditions = [];

foreach ($updateData as $id => $qty) {

$case = $connection->quoteInto('?', $id);

$result = $connection->quoteInto('?', $qty);

$conditions[$case] = $result;

}

$value = $connection->getCaseSql('id', $conditions, 'qty');

$where = ['id IN (?)' => array_keys($updateData)];

try {   

    $connection->beginTransaction();

    $connection->update($this->getTable('my_custom_table'), ['qty' => $value], $where);

    $connection->commit();

} catch(\Exception $e) {

    $connection->rollBack();

}
  • There is also one more example of adding multiple records. Im mentioning that as my custom resource sample.

/app/code/[NameSpace]/[ModuleName]/Model/ResourceModel/Custom.php

This solution is better than save () method of model in a loop.

Last Update: August 21, 2019  

March 15, 2018   1062   Nandini R    Operations    
Total 2 Votes:
1

Tell us how can we improve this post?

+ = Verify Human or Spambot ?

Leave a Reply

Your email address will not be published. Required fields are marked *

Facebook
Twitter
INSTAGRAM
LinkedIn