the snippet below is the steps to follow to resolve your issue
$mainImage = $this->imageQueryResource->getProductMainImage($productId); $firstImage = $this->imageQueryResource->getFirstImages($productId); $output->writeln('first image' . $firstImage); if ($mainImage != $firstImage) { $output->writeln('fix product ' . $mainImage . ' product id: ' . $productId); $otherImages = $this->imageQueryResource->getAllImages($productId); foreach ($otherImages as $image) { $position = 1; if ($image['value'] == $mainImage) { $this->imageQueryResource->updatePosition((int)$image['value_id'], $position); } } reset($otherImages); foreach ($otherImages as $image) { if ($image['value'] != $mainImage) { $position++; $this->imageQueryResource->updatePosition((int)$image['value_id'], $position); } } }
the file below does the query and update for your images.
<?php namespace Mbs\ImageSorting\Model; use Magento\Framework\App\ResourceConnection; class ImageQueryResource { /** * @var ResourceConnection */ private $resourceConnection; /** * @var \Magento\Eav\Model\Config */ private $config; public function __construct( ResourceConnection $resourceConnection, \Magento\Eav\Model\Config $config ) { $this->resourceConnection = $resourceConnection; $this->config = $config; } public function getProductMainImage(int $productId) { $mainImageAttribute = $this->config->getAttribute(\Magento\Catalog\Model\Product::ENTITY, 'image'); $select = $this->resourceConnection->getConnection()->select(); $select->from($this->resourceConnection->getTableName('catalog_product_entity_' . $mainImageAttribute->getBackendType())); $select->where('entity_id=?', $productId); $select->where('attribute_id=?', $mainImageAttribute->getId()); $select->reset(\Magento\Framework\DB\Select::COLUMNS); $select->columns(['value']); return $this->resourceConnection->getConnection()->fetchOne($select); } /** * @param int $productId * select * from catalog_product_entity_media_gallery_value as v inner join catalog_product_entity_media_gallery as g on g.value_id=v.value_id where entity_id=1802 order by position */ public function getAllImages(int $productId) { $select = $this->resourceConnection->getConnection()->select(); $select->from(['v' => $this->resourceConnection->getTableName('catalog_product_entity_media_gallery_value')]); $select->join( ['g' => 'catalog_product_entity_media_gallery'], 'g.value_id=v.value_id', [] ); $select->where('entity_id=?', $productId); $select->reset(\Magento\Framework\DB\Select::COLUMNS); $select->columns(['v.value_id', 'v.position', 'g.value']); $select->order('v.position'); return $this->resourceConnection->getConnection()->fetchAll($select); } /** * @param int $productId * select * from catalog_product_entity_media_gallery_value as v inner join catalog_product_entity_media_gallery as g on g.value_id=v.value_id where entity_id=1802 order by position */ public function getFirstImages(int $productId) { $select = $this->resourceConnection->getConnection()->select(); $select->from(['v' => $this->resourceConnection->getTableName('catalog_product_entity_media_gallery_value')]); $select->join( ['g' => 'catalog_product_entity_media_gallery'], 'g.value_id=v.value_id', [] ); $select->where('entity_id=?', $productId); $select->reset(\Magento\Framework\DB\Select::COLUMNS); $select->columns(['g.value', 'v.position']); $select->order('v.position'); //$select->limit(1); $result = $this->resourceConnection->getConnection()->fetchAll($select); $minPosition = 2000; $position = []; $minImage = ''; foreach ($result as $row) { if ($row['position'] < $minPosition) { $minPosition = $row['position']; $minImage = $row['value']; $position[$row['position']] = 1; } } if (count($position) > 1) { return $minImage; } } public function updatePosition(int $valueId, int $position) { $this->resourceConnection->getConnection()->update( $this->resourceConnection->getTableName('catalog_product_entity_media_gallery_value'), ['position' => $position], ['value_id=?' => $valueId] ); } }
