Best way for one time huge data insert to new custom table

Question

I have created a new table. I will have to loop through all customers, its orders, its items details, its product details and insert to new custom table. It is a one time process. Should I use data patch? If so, does it halt during setup upgrade on huge data. Can you please suggest the best way to do it.

Answer 1

A data patch is fine, the issue you may bump into depending on the size of these collections is that you're exhausting available memory. If this is the case you'll need to iterate through the collections rather than loading them all at once. There's a good explanation of this process here:

https://www.matheusgontijo.com/2018/02/10/magento-2-working-with-large-collections-php-fatal-error-allowed-memory-size-of-xxxx-bytes-exhausted

With that in mind you could use the following as a starting point which incorporates both an iterator and insertMultiple as suggested by Sukumar. You would need to adjust the collection to fit your needs and replace the placeholder data where appropriate.

<?php

namespace Vendor\Module\Setup\Patch\Data;

use Magento\Framework\Setup\Patch\DataPatchInterface;
use Magento\Framework\Setup\ModuleDataSetupInterface;
use Magento\Framework\Model\ResourceModel\Iterator;
use Magento\Catalog\Api\ProductRepositoryInterface;
use Magento\Sales\Model\ResourceModel\Order\CollectionFactory;

class InsertOrderData implements DataPatchInterface
{
    /**
     * @var ModuleDataSetupInterface
     */
    private $moduleDataSetup;

    /**
     * @var CollectionFactory
     */
    private $orderCollectionFactory;

    /**
     * @var ProductRepositoryInterface
     */
    private $productRepository;

    private $dataToInsert = [];

    public function __construct(
        ModuleDataSetupInterface   $moduleDataSetup,
        CollectionFactory          $orderCollectionFactory,
        ProductRepositoryInterface $productRepository
    ) {
        $this->moduleDataSetup = $moduleDataSetup;
        $this->orderCollectionFactory = $orderCollectionFactory;
        $this->productRepository = $productRepository;
    }

    public function apply()
    {
        $connection = $this->moduleDataSetup->getConnection();

        // build your order collection
        $orders = $this->orderCollectionFactory->create();
        // Create a new iterator for the order collection
        $iterator = new Iterator($orders);

        // iterate collection
        $iterator->walk(
            $orders->getSelect(),
            [[$this, 'callback']]
        );


        // Insert the data into the database
        $connection->insertMultiple('your_table_name', $this->dataToInsert);
    }

    public function callback($args)
    {
        // load of the product
        $product = $this->productRepository->getById($args['row']['entity_id']);

        $this->dataToInsert[] = [
            'order_id'      => $order->getId(),
            'customer_name' => $order->getCustomerName(),
            'grand_total'   => $order->getGrandTotal(),
            // Add any other fields you need to insert here
        ];
    }

    public function getAliases()
    {
        // Return any module aliases here
        return [];
    }

    public static function getDependencies()
    {
        // Return any module dependencies here
        return [];
    }
}