I have a requirement to assign the 4th image of a product a custom attribute 'hover_image', which will be shown when a user hovers over the product on PLP. I have done this using position value stored in DB in table catalog_product_entity_media_gallery_value but it seems that it's not necessarily giving the 4th image of a product and that's why the attribute is assigned to 5th image of some products.

here is my code of the sql query to select products

               $select = $connection->select()
                    ['ent' => $connection->getTableName('catalog_product_entity'),]
                    ['web' => $connection->getTableName('catalog_product_website')],
                    'ent.entity_id = web.product_id',
                    ['gVal' => $connection->getTableName('catalog_product_entity_media_gallery_value')],
                    'gVal.row_id = ent.row_id AND gVal.store_id = 0',
                    ['if (sVal.store_id, sVal.store_id, gVal.store_id) as store_id']
                    ['sVal' => $connection->getTableName('catalog_product_entity_media_gallery_value')],
                    'sVal.row_id = ent.row_id AND sVal.store_id = ' . $storeId,
                    ['gal' => $connection->getTableName('catalog_product_entity_media_gallery')],
                    'gal.value_id = if (sVal.value_id, sVal.value_id, gVal.value_id)',
                    'if (sVal.position, sVal.position, gVal.position) = ?',
                    // hover_image is assigned only if product has position 4 image, otherwise attribute empty
                )->where('web.website_id = ?', $websiteId)->group('ent.entity_id');

here is the code for saving products

$connection = $this->resourceConnection->getConnection();
        try {
        } catch (Exception $e) {
            throw $e;


Your Answer

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