Base images as first image, change positions in bulk - Magento 2.3.5

Question

We have a large catalog (125K products), with a lot of products where the base image is positioned somewhere in the middle of the gallery.

The selection of base image itself is fine, this should be and remain the base image, we only need to change the position of this image in the gallery.

We know that we can manually change the sorting of the images and place this image in front of the gallery, but is there a way to do it in bulk for all products?

Something like a sql query, that change the position of the base image and place that one in front?

In the screenshots below you see the first, where the base image is in the middle of the gallery and the last image as it should be. Where the base image is placed as first in the gallery.

Before:

After:

Answer 1

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]
        );
    }

}