1

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 enter image description here

3 Answers 3

2

I write small resource helper (resolver) which you can use

app/code/Acme/StackExchange/Model/ResourceModel/Catalog/Product/PositionResolver.php

<?php
declare(strict_types=1);

namespace Acme\StackExchange\Model\ResourceModel\Catalog\Product;

use Magento\Framework\Model\ResourceModel\Db\AbstractDb;

class PositionResolver extends AbstractDb
{
    /**
     * @inheritDoc
     */
    protected function _construct()
    {
        $this->_init('catalog_product_entity', 'entity_id');
    }

    public function update(int $categoryId, int $productId, int $position): bool
    {
        $adapter = $this->getConnection();
        $select  = $adapter->select()
            ->from(
                ['ccp' => $this->getTable('catalog_category_product')],
                ['position']
            )
            ->join(
                ['cpe' => $this->getTable('catalog_product_entity')],
                'ccp.product_id = cpe.entity_id',
                null
            )
            ->where('ccp.category_id = ?', $categoryId)
            ->where('ccp.product_id = ?', $productId);

        $oldPosition = $adapter->fetchOne($select);
        if ($oldPosition === false) {
            return false;
        }
        $oldPosition = (int)$oldPosition;

        $maxPosition = $this->getMax($categoryId);
        if ($position < $maxPosition && $position !== $oldPosition) {
            $positionExpr = new \Zend_Db_Expr('position + 1');
            $where        = [
                'category_id = ?' => $categoryId,
            ];
            if ($oldPosition > $position) {
                $where['position < ?']  = $oldPosition;
                $where['position >= ?'] = $position;
            } else {
                $positionExpr           = new \Zend_Db_Expr('position - 1');
                $where['position > ?']  = $oldPosition;
                $where['position <= ?'] = $position;
            }
            $adapter->update(
                $this->getTable('catalog_category_product'),
                ['position' => $positionExpr],
                $where
            );
        }

        $adapter->update(
            $this->getTable('catalog_category_product'),
            ['position' => $position],
            [
                'category_id = ?' => $categoryId,
                'product_id = ?'  => $productId,
            ]
        );

        return true;
    }

    public function getMax(int $categoryId): int
    {
        $adapter = $this->getConnection();
        $select  = $adapter->select()
            ->from(
                ['ccp' => $this->getTable('catalog_category_product')],
                new \Zend_Db_Expr('MAX(position)')
            )
            ->join(
                ['cpe' => $this->getTable('catalog_product_entity')],
                'ccp.product_id = cpe.entity_id',
                null
            )
            ->where('ccp.category_id = ?', $categoryId);

        return (int)$adapter->fetchOne($select);
    }
}

how to use

/** @var \Magento\Catalog\Model\ResourceModel\Product $resource */
/** @var \Acme\StackExchange\Model\ResourceModel\Catalog\Product\PositionResolver $resolver */
/** @var string $sku */
/** @var string|int $categoryId */
/** @var string|int $position */
        
$productId = $resource->getIdBySku($sku);
if ($productId !== false) {
    $resolver->update((int)$categoryId, (int)$productId, (int)$position);
}
5
  • $oldPosition = $adapter->fetchOne($select); if ($oldPosition === false) { return false; } this line of code not getting old position, Aug 15, 2022 at 8:22
  • I've tested provided fragment before post it and it should work. You need to provide more details what exactly doesn't work. The code is not insert any new products to category. You should also check that you have product and it relation in catalog_category_product. Aug 15, 2022 at 8:28
  • see my answer for details Aug 15, 2022 at 9:22
  • what you think about this approach? Aug 15, 2022 at 12:11
  • This code works fine when there is only one product but when there's are multiple products in csv file it only add the last sku to the exact position , the code is not optimized for multiple products position change at one time. – Sep 1, 2022 at 17:35
1

but issue is that when i import csv it changes the product position and displays it in last position in grid but when i debug it, it shows that product is in given position where is was supposed to be as you can in picture its in given position [enter image description here]

but in grid it shows in last position. i also run index:reindex command but it still shows it last [![enter image description here

enter image description here

This Code somehow works, but not ideal aproach

$category = $this->categoryFactory->create()->load($categoryId);
                        $products = $category->getProductsPosition();
                        $catProducts = $category->getProductCollection()->addAttributeToSelect('*');
                        foreach ($catProducts as $pro) {
                            $id = $pro->getId();
                            if ($id == $productId) {
                                $pid = $pro->getId();
                            }
                        }
                        foreach ($products as $id => $value) {
                            if (in_array($id, $pid)) {
                                $products[$id] = $position;
                            }
                        }
                        $category->setPostedProducts($products);
                        $category->save();
5
  • If you start to debug, you can see that $oldPosition is present. Please check, what is data in catalog_category_product for your product/category first? Aug 15, 2022 at 10:25
  • yeah, you are right, but the issue is the position of the product in grid , and the position of the product in that table is different, i need to look more Aug 15, 2022 at 10:47
  • this code create issues when there are multiple products in csv Aug 15, 2022 at 12:11
  • 1
    Both logic should work. If you correctly used provided resolver it also should work. If you want to simplify save efforts i suggest to see \Magento\Catalog\Model\ResourceModel\Category::_saveCategoryProducts. This is original logic to save positions. I guess you made mistake somewhere in data. Finally both code will update the same data. With your logic you missed move other product positions. One of observer will normalize positions, but you still can receive some mess for partial update. Aug 15, 2022 at 14:02
  • thanks, but i still cant figure out why is not grid updating when position is changed using your code Aug 15, 2022 at 18:23
1

Note: In path should be Adminhtml. I've updated some code but general goal is add logic to run reindex process

Vendor\Module\Controller\Adminhtml\Import\Index.php

<?php
declare(strict_types=1);

namespace Vendor\Module\Controller\Adminhtml\Import;

use Magento\Backend\App\Action;
use Magento\Backend\App\Action\Context;
use Magento\Catalog\Api\CategoryRepositoryInterface;
use Magento\Catalog\Api\ProductRepositoryInterface;
use Magento\Catalog\Model\CategoryFactory;
use Magento\Catalog\Model\Indexer\Category\Product as CategoryProductIndexer;
use Magento\Catalog\Model\ResourceModel\Product as ProductRepository;
use Magento\Framework\App\Config\ScopeConfigInterface;
use Magento\Framework\App\Filesystem\DirectoryList;
use Magento\Framework\Controller\Result\Redirect;
use Magento\Framework\Controller\ResultFactory;
use Magento\Framework\File\Csv;
use Magento\Framework\View\Result\PageFactory;
use Magento\Store\Model\ScopeInterface;
use Vendor\Module\Model\ResourceModel\Catalog\Product\PositionResolver;

class Index extends Action
{
    public const ADMIN_RESOURCE     = 'Vendor_Module::sortbycsv';
    public const XML_PATH_NAME_FILE = 'sortbycsv/sortbycsv/csv_file_upload';

    protected ScopeConfigInterface        $scopeConfig;
    protected PageFactory                 $resultPageFactory;
    protected CategoryFactory             $categoryFactory;
    protected ResultFactory               $resultRedirect;
    protected ProductRepositoryInterface  $productRepositoryInterface;
    protected CategoryRepositoryInterface $categoryRepositoryInterface;
    protected Csv                         $csvProcessor;
    protected DirectoryList               $directoryList;
    protected PositionResolver            $positionResolver;
    protected CategoryProductIndexer      $categoryProductIndexer;
    protected ProductRepository           $productResource;

    public function __construct(
        Context $context,
        Csv $csvProcessor,
        PageFactory $resultPageFactory,
        DirectoryList $directoryList,
        ResultFactory $resultRedirect,
        CategoryFactory $categoryFactory,
        ScopeConfigInterface $scopeConfig,
        ProductRepositoryInterface $productRepositoryInterface,
        CategoryRepositoryInterface $categoryRepositoryInterface,
        ProductRepository $productResource,
        PositionResolver $positionResolver,
        CategoryProductIndexer $categoryProductIndexer
    ) {
        $this->resultRedirect              = $resultRedirect;
        $this->directoryList               = $directoryList;
        $this->csvProcessor                = $csvProcessor;
        $this->scopeConfig                 = $scopeConfig;
        $this->categoryFactory             = $categoryFactory;
        $this->resultPageFactory           = $resultPageFactory;
        $this->productRepositoryInterface  = $productRepositoryInterface;
        $this->categoryRepositoryInterface = $categoryRepositoryInterface;
        $this->productResource             = $productResource;
        $this->positionResolver            = $positionResolver;
        $this->categoryProductIndexer      = $categoryProductIndexer;

        parent::__construct($context);
    }

    public function execute(): Redirect
    {
        $resultRedirect = $this->resultRedirectFactory->create();
        $url            = $this->_redirect->getRefererUrl();
        $resultRedirect->setUrl($url);

        if (!$this->readCsv()) {
            $this->messageManager->addErrorMessage(
                "Csv sheet should only have product_sku, sorting_position and category_id. "
            );
            return $resultRedirect;
        }

        $this->messageManager->addSuccessMessage("CSV imported successfully");
        return $resultRedirect;
    }

    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->csvProcessor->getData($file);
            if (!$this->validateCsv($csvData)) {
                return false;
            }

            $categoryIds      = [];
            $csvDataProcessed = [];
            unset($csvData[0]);
            [$csvDataProcessed] = $this->csvProcessValues($csvData, $csvDataProcessed);
            foreach ($csvDataProcessed as $dataRow) {
                $sku        = $dataRow['sku'];
                $position   = (int)$dataRow['position'];
                $categoryId = (int)$dataRow['category_id'];

                if ($sku) {
                    $productId = $this->productResource->getIdBySku($sku);
                    if ($productId) {
                        try {
                            if (!$this->positionResolver->update($categoryId, (int)$productId, $position)) {
                                throw new \Exception('');
                            }
                            $categoryIds[$categoryId] = $categoryId;
                        } catch (\Exception $e) {
                            $this->messageManager->addErrorMessage(__(" %1 position is not updated. ", $sku));
                        }

                        $this->messageManager->addSuccessMessage(__(' %1  Position Updated Successfully.', $sku));
                    } else {
                        $this->messageManager->addErrorMessage(__("%1 is not available", $sku));
                    }
                } else {
                    $this->messageManager->addErrorMessage(__("sku %1 is not available", $sku));
                }
            }

            if ($categoryIds) {
                $this->categoryProductIndexer->execute($categoryIds);
            }
        }

        return true;
    }

    public function getConfigFileName(string $nameFile = self::XML_PATH_NAME_FILE)
    {
        return $this->scopeConfig->getValue($nameFile, ScopeInterface::SCOPE_STORE);
    }

    private function validateCsv(array $csvData): bool
    {
        foreach ($csvData as $csvValue) {
            if (count($csvValue) < 3 || $csvValue[0] == null) {
                return false;
            }
        }
        return true;
    }

    private function csvProcessValues(array $csvData, array $csvDataProcessed): array
    {
        foreach ($csvData as $csvValue) {
            $csvValueProcessed = [];

            foreach ($csvValue as $key => $value) {

                if ($key == 0) {
                    $csvValueProcessed['sku'] = $value;
                }

                if ($key == 1) {
                    $csvValueProcessed['position'] = $value;
                }

                if ($key == 2) {
                    $csvValueProcessed['category_id'] = $value;
                }
            }
            $csvDataProcessed[] = $csvValueProcessed;
        }
        return [$csvDataProcessed];
    }
}

Vendor\Module\Model\ResourceModel\Catalog\Product\PositionResolver.php

    <?php
    declare(strict_types=1);
    
    namespace Vendor\Module\Model\ResourceModel\Catalog\Product;
    
    use Magento\Framework\Model\ResourceModel\Db\AbstractDb;
    use Zend_Db_Expr;
    
    /**
     * Resource Helper
     */
    class PositionResolver extends AbstractDb
    {
        /**
         * @param int $categoryId
         * @param int $productId
         * @param int $position
         * @return bool
         */
        public function update(int $categoryId, int $productId, int $position): bool
        {
            $adapter = $this->getConnection();
            $select = $adapter->select()
                ->from(
                    ['ccp' => $this->getTable('catalog_category_product')],
                    ['position']
                )
                ->join(
                    ['cpe' => $this->getTable('catalog_product_entity')],
                    'ccp.product_id = cpe.entity_id',
                    null
                )
                ->where('ccp.category_id = ?', $categoryId)
                ->where('ccp.product_id = ?', $productId);
    
            $oldPosition = $adapter->fetchOne($select);
            if ($oldPosition === false) {
                return false;
            }
            $oldPosition = (int)$oldPosition;
    
            $maxPosition = $this->getMax($categoryId);
            if ($position < $maxPosition && $position !== $oldPosition) {
                $positionExpr = new Zend_Db_Expr('position + 1');
                $where = [
                    'category_id = ?' => $categoryId,
                ];
                if ($oldPosition > $position) {
                    $where['position < ?'] = $oldPosition;
                    $where['position >= ?'] = $position;
                } else {
                    $positionExpr = new Zend_Db_Expr('position - 1');
                    $where['position > ?'] = $oldPosition;
                    $where['position <= ?'] = $position;
                }
                $adapter->update(
                    $this->getTable('catalog_category_product'),
                    ['position' => $positionExpr],
                    $where
                );
            }
    
            $adapter->update(
                $this->getTable('catalog_category_product'),
                ['position' => $position],
                [
                    'category_id = ?' => $categoryId,
                    'product_id = ?' => $productId,
                ]
            );
    
            return true;
        }
    
        /**
         * @param int $categoryId
         * @return int
         */
        public function getMax(int $categoryId): int
        {
            $adapter = $this->getConnection();
            $select = $adapter->select()
                ->from(
                    ['ccp' => $this->getTable('catalog_category_product')],
                    new Zend_Db_Expr('MAX(position)')
                )
                ->join(
                    ['cpe' => $this->getTable('catalog_product_entity')],
                    'ccp.product_id = cpe.entity_id',
                    null
                )
                ->where('ccp.category_id = ?', $categoryId);
    
            return (int)$adapter->fetchOne($select);
        }
    
        /**
         * @inheritDoc
         */
        protected function _construct()
        {
            $this->_init('catalog_product_entity', 'entity_id');
        }
    }
3
  • Try updated one Aug 16, 2022 at 2:48
  • This code works fine when there is only one product but when there's are multiple products in csv file it only add the last sku to the exact position , the code is not optimized for multiple products position change at one time. Sep 1, 2022 at 16:43
  • which class should i look into for this issue? Sep 1, 2022 at 16:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.