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

Publié par David le 21 septembre 202321 septembre 2023

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;

-- 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;

-- 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;

-- 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;

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

Update 2023

Introduction

Disabling the combination with 0-stock

Set the default combination images as cover images

If Advanced Stock Module is enabled

0 commentaire

Laisser un commentaire Annuler la réponse

[Update 2024] Envoyer sa marchandise en 2024 avec MyFlyingBox et Delivengo (Optimisé Prestashop)

Prestashop – Save button do not display

Adapter la Taille des Images Transporteurs sur Prestashop Thème Warehouse

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

Update 2023

Introduction

Disabling the combination with 0-stock

Set the default combination images as cover images

If Advanced Stock Module is enabled

0 commentaire

Laisser un commentaire Annuler la réponse

Articles similaires

[Update 2024] Envoyer sa marchandise en 2024 avec MyFlyingBox et Delivengo (Optimisé Prestashop)

Prestashop – Save button do not display

Adapter la Taille des Images Transporteurs sur Prestashop Thème Warehouse