2

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:

enter image description here

After:

enter image description here

  • You may try with Store Manager. Please follow the steps in the link, mag-manager.com/product-information/magento-product-management/… – Siva Aug 17 '20 at 13:02
  • @Siva Thanks! But this will be a one time job, that we therefore like to run without something like Store Manager. – JGeer Aug 17 '20 at 13:08
  • 1
    can you put a screenshot of how it looks in the backend with the issue and when resolved manually. This way, we can focus at automating what you do manually – Herve Tribouilloy Aug 17 '20 at 20:27
  • @HerveTribouilloy Thanks! Sure, see the added images. – JGeer Aug 18 '20 at 6:16
1
+100

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

}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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