Magento2.4.2: How to add product to a given position in given category?

[iwogujekpiaab5]

I'm trying to add a product in a given category in given position like in my csv file im giving SKU,Position,CategoryId and i want to add that product in that category in given position Here is my code

public function readCsv(): bool
    {
        $pubMediaDir = $this->directoryList->getPath(DirectoryList::MEDIA);
        $fieName = $this->getConfigFileName();
        $ds = DIRECTORY_SEPARATOR;
        $dirTest = '/csvsortoptions';

        $file = $pubMediaDir . $dirTest . $ds . $fieName;

        if (!empty($file)) {
            $csvData = $this->csvProccesor->getData($file);
            if (!$this->validateCsv($csvData)) {
                return false;
            }

            $csvDataProcessed = [];
            unset($csvData[0]);
            list($csvDataProcessed) = $this->csvProcessValues($csvData, $csvDataProcessed);

            foreach ($csvDataProcessed as $dataRow) {

                $sku = $dataRow['sku'];
                $position = $dataRow['position'];
                $categoryId = $dataRow['category_id'];

                $adapter = $this->getConnection;
                if ($sku) {
                    $productId = $this->productRepositoryInterface->get($sku)->getId();
                    if ($productId) {
                        $where = array(
                            'category_id = ?' => (int)$categoryId,
                            'product_id = ?' => (int)$productId
                        );
                        $bind = array('position' => (int)$position);
                        $adapter->update($this->categoryProductTable, $bind, $where);
                        $this->messageManager->addSuccessMessage(__(' %1  Position Updated Successfully.', $sku));
                    } else {
                        $this->messageManager->addErrorMessage(__("%1 is not available", $sku));
                    }
                } else {
                    $this->messageManager->addErrorMessage(__("sku is not available"));
                }
            }
        }
        return true;
    }

and it should add that product to given position and one thing important here to note is it shouldn't replace the existing product in that position but move other products position down/decrease accordingly

[Bhanu Periwal]

Hello @iwogujekpiaab5 

You can refer below code which we have used:
In controller file:

public function execute()
        {
   $categoryId = $this->getRequest()->getParam('category');
            $fileName = $_FILES["upload-stock-csv"]["name"];
            $filetmpName = $_FILES["upload-stock-csv"]["tmp_name"];           

            if (isset($fileName) &&  (pathinfo($fileName, PATHINFO_EXTENSION) == 'csv')) {
                //Update product position using csv
                $positionUpdate = $this->_csvProcessor->getData($filetmpName);
                $this->_position->updatePositionUsingsku($categoryId, $positionUpdate);
            } else {
                $this->messageManager->addError(__("Please select the valid file format"));
            }
        }

In Model file:

use Magento\Catalog\Model\Category;
use Magento\Catalog\Model\Product;
use Magento\Framework\Message\ManagerInterface;
use Magento\Framework\App\ResourceConnection;

class Position
{

    protected $_messageManager;
    protected $_productRepository;
    protected $_categoryRepository;

    public function __construct(Category $categoryRepository, Product $productRepository, ResourceConnection $resourceConnection, ManagerInterface $messageManager)
    {
        $this->_categoryRepository = $categoryRepository;
        $this->_productRepository = $productRepository;
        $this->_messageManager = $messageManager;
        $this->_getConnection = $resourceConnection->getConnection();
        $this->_categoryProductTable = $resourceConnection->getTableName('catalog_category_product');
    }


    public function updatePositionUsingsku($categoryId, $positionUpdate)
    {
        $adapter = $this->_getConnection;
        foreach ($positionUpdate as $row) {
            if ((trim($row[0]) == 'Sku') || (trim($row[0]) == 'SKU') || (trim($row[0]) == 'sku'))
                continue;
            $sku = trim($row[0]);
            $productPosition = trim($row[1]);
            //Update product position using csv
            if ($sku) {
                $productId = $this->_productRepository->getIdBySku($sku);
                if ($productId) {
                    $where = array(
                        'category_id = ?' => (int) $categoryId,
                        'product_id = ?' => (int) $productId
                    );
                    $position = $productPosition;
                    $bind = array('position' => (int) $position);
                    $adapter->update($this->_categoryProductTable, $bind, $where);
                    $this->_messageManager->addSuccess(__(' %1  Position Updated Successfully.', $sku));
                } else {
                    $this->_messageManager->addError(__("%1 is not available", $sku));
                }
            } else {
                $this->_messageManager->addError(__("sku is not available"));
            }
        }
    }

}

It may help you!
Thank you