[UPDATE 2023] Use the default combination image, with a non-zero quantity, as the cover image in Prestashop

Update 2023

I was really hanging over a problem with a website. In fact, the products have a lot of combination (colors). Because it is related with fashion accessories. The thing is, some colors were simply not remade because of the complexity of certain material. So, a lot of products miniature in product-list were displaying « out-of-stock » or « Product available with different options » and it will never change!

Before SQL script

After SQL script

I just thought it would be interesting to create an Add-on to the SQL script I designed on my blog, which would be:

• Check all the products combinations which are the default combination and have a stock quantity of zero
  • Set them to not the default combination
  • Use the combination of this product with the max stock quantity as default
• Use the old SQL script: set the cover images as the images of the default combination

Introduction

You can import the default image of a product with CSV import, but you cannot import the cover image. For example, it is very important to select a good bunch of images for the products with different colors. If you sell t-shirts and all the cover images show white t-shirts, your webshop will lack of identity.

The easiest way is to import the default image and use a SQL request to set the same cover image than the default image.

Disabling the combination with 0-stock

-- Start a transaction for the ps_product_attribute table operations
START TRANSACTION;

-- Set default_on = NULL for rows where quantity = 0 and default_on = 1
UPDATE ps_product_attribute
SET default_on = NULL
WHERE quantity = 0 AND default_on = 1;

-- Set default_on = 1 for a single row with the highest quantity for each id_product 
-- but ignore those products where any row has default_on=1 and quantity is not 0.
UPDATE ps_product_attribute p
JOIN (
    SELECT pa.id_product, pa.quantity,
           ROW_NUMBER() OVER (PARTITION BY pa.id_product ORDER BY pa.quantity DESC) AS rn
    FROM ps_product_attribute pa
    WHERE pa.id_product NOT IN (
        SELECT distinct id_product
        FROM ps_product_attribute
        WHERE default_on=1 AND quantity <> 0
    )
) t
ON p.id_product = t.id_product AND p.quantity = t.quantity
SET p.default_on = CASE
    WHEN t.rn = 1 THEN 1
    ELSE NULL
    END;

COMMIT;

-- Start a transaction for the ps_product_attribute_shop table operations
START TRANSACTION;

-- Step 1: Set all default_on values in ps_product_attribute_shop to NULL
UPDATE ps_product_attribute_shop
SET default_on = NULL;

-- Step 2: Update default_on in ps_product_attribute_shop to match ps_product_attribute for each id_product_attribute
UPDATE ps_product_attribute_shop pshop
JOIN ps_product_attribute p ON pshop.id_product_attribute = p.id_product_attribute
SET pshop.default_on = p.default_on;

COMMIT;

-- Start a transaction
START TRANSACTION;

UPDATE ps_product p
JOIN ps_product_attribute pa 
ON p.id_product = pa.id_product
SET p.cache_default_attribute = pa.id_product_attribute
WHERE pa.default_on = 1;

UPDATE ps_product_shop ppshop
JOIN ps_product pp ON ppshop.id_product = pp.id_product
SET ppshop.cache_default_attribute = pp.cache_default_attribute;

-- Commit the transaction
COMMIT;

Set the default combination images as cover images

• First, you need to have set all the default image in your catalog. You can do this automatically by importing a CSV file.
• Open your database (for example by PhpMyAdmin)
• Create backup of your database for safety (export data and structure)
• Open SQL tab and perform queries from this file (change ‘ps_’ to your prefix):

-- Update cover attributes in ps_image and ps_image_shop tables
START TRANSACTION;

-- Reset covers in ps_image and ps_image_shop
UPDATE ps_image SET cover = NULL;
UPDATE ps_image_shop SET cover = NULL;

-- Set cover attributes based on default_on
UPDATE IGNORE ps_image
SET cover = 1
WHERE ps_image.id_image IN (
 SELECT ps_product_attribute_image.id_image
 FROM ps_product_attribute
 INNER JOIN ps_product_attribute_image
 ON ps_product_attribute.id_product_attribute = ps_product_attribute_image.id_product_attribute
 AND ps_product_attribute.default_on = 1
);

UPDATE IGNORE ps_image_shop
SET cover = 1
WHERE ps_image_shop.id_image IN (
 SELECT ps_product_attribute_image.id_image
 FROM ps_product_attribute
 INNER JOIN ps_product_attribute_image
 ON ps_product_attribute.id_product_attribute = ps_product_attribute_image.id_product_attribute
 AND ps_product_attribute.default_on = 1
);

COMMIT;

If Advanced Stock Module is enabled

The script would change a little. Here is the total SQL:

-- Start a transaction for the ps_product_attribute table operations
START TRANSACTION;

-- Set default_on = NULL for rows where quantity = 0 and default_on = 1
UPDATE ps_product_attribute pa
JOIN ps_stock_available sa ON pa.id_product_attribute = sa.id_product_attribute
SET pa.default_on = NULL
WHERE sa.quantity = 0 AND pa.default_on = 1;


-- Set default_on = 1 for a single row with the highest quantity for each id_product 
-- but ignore those products where any row has default_on=1 and quantity is not 0.
UPDATE ps_product_attribute p
JOIN (
    SELECT pa.id_product, pa.id_product_attribute, sa.quantity,
           ROW_NUMBER() OVER (PARTITION BY pa.id_product ORDER BY sa.quantity DESC) AS rn
    FROM ps_product_attribute pa
    JOIN ps_stock_available sa ON pa.id_product_attribute = sa.id_product_attribute
    WHERE pa.id_product NOT IN (
        SELECT DISTINCT id_product
        FROM ps_product_attribute pa_inner
        WHERE default_on = 1 AND EXISTS (
            SELECT 1
            FROM ps_stock_available
            WHERE id_product_attribute = pa_inner.id_product_attribute AND quantity <> 0
        )
    )
) t
ON p.id_product = t.id_product AND p.id_product_attribute = t.id_product_attribute
SET p.default_on = CASE
    WHEN t.rn = 1 THEN 1
    ELSE NULL
    END;


COMMIT;

-- Start a transaction for the ps_product_attribute_shop table operations
START TRANSACTION;

-- Step 1: Set all default_on values in ps_product_attribute_shop to NULL
UPDATE ps_product_attribute_shop
SET default_on = NULL;

-- Step 2: Update default_on in ps_product_attribute_shop to match ps_product_attribute for each id_product_attribute
UPDATE ps_product_attribute_shop pshop
JOIN ps_product_attribute p ON pshop.id_product_attribute = p.id_product_attribute
SET pshop.default_on = p.default_on;

COMMIT;

-- Start a transaction
START TRANSACTION;

UPDATE ps_product p
JOIN ps_product_attribute pa 
ON p.id_product = pa.id_product
SET p.cache_default_attribute = pa.id_product_attribute
WHERE pa.default_on = 1;

UPDATE ps_product_shop ppshop
JOIN ps_product pp ON ppshop.id_product = pp.id_product
SET ppshop.cache_default_attribute = pp.cache_default_attribute;

-- Commit the transaction
COMMIT;


-- Update cover attributes in ps_image and ps_image_shop tables
START TRANSACTION;

-- Reset covers in ps_image and ps_image_shop
UPDATE ps_image SET cover = NULL;
UPDATE ps_image_shop SET cover = NULL;

-- Set cover attributes based on default_on
UPDATE IGNORE ps_image
SET cover = 1
WHERE ps_image.id_image IN (
 SELECT ps_product_attribute_image.id_image
 FROM ps_product_attribute
 INNER JOIN ps_product_attribute_image
 ON ps_product_attribute.id_product_attribute = ps_product_attribute_image.id_product_attribute
 AND ps_product_attribute.default_on = 1
);

UPDATE IGNORE ps_image_shop
SET cover = 1
WHERE ps_image_shop.id_image IN (
 SELECT ps_product_attribute_image.id_image
 FROM ps_product_attribute
 INNER JOIN ps_product_attribute_image
 ON ps_product_attribute.id_product_attribute = ps_product_attribute_image.id_product_attribute
 AND ps_product_attribute.default_on = 1
);

COMMIT;