Prestashop Consultas query sql Productos e imágenes

Lo que use para localizar las imágenes que se habían repetido por el servicio externo mal ejecutado al cambiar versión de PHP:

SELECT p.id_product, p.id_image
,CONCAT(
    './p/',
    IF(CHAR_LENGTH(p.id_image) >= 6, 
        CONCAT(
        SUBSTRING(p.id_image, -6, 1),
        '/'),
        ''),
	IF(CHAR_LENGTH(p.id_image) >= 5, CONCAT(SUBSTRING(p.id_image, -5, 1), '/'), ''),        
    IF(CHAR_LENGTH(p.id_image) >= 4, CONCAT(SUBSTRING(p.id_image, -4, 1), '/'), ''),
    IF(CHAR_LENGTH(p.id_image) >= 3, CONCAT(SUBSTRING(p.id_image, -3, 1), '/'), ''),
    IF(CHAR_LENGTH(p.id_image) >= 2, CONCAT(SUBSTRING(p.id_image, -2, 1), '/'), ''),
    IF(CHAR_LENGTH(p.id_image) >= 1, CONCAT(SUBSTRING(p.id_image, -1, 1), '/'), ''),
    p.id_image,
    '.jpg')  AS 'Images'
FROM ps_image p
where id_image > (select min(p1.id_image)+100 from ps_image p1 where p1.id_product = p.id_product)
order by id_product, id_image

Y creada la hoja de cálcula, añadir 2 columnas:

=+CONCATENAR("mv ";C2; " ";C2;"_onsat;";"mv ";EXTRAE(C2;1;22); "-cart_default.jpg ";EXTRAE(C2;1;22);"-cart_default.jpg_onsat;";"mv ";EXTRAE(C2;1;22); "-home_default.jpg ";EXTRAE(C2;1;22);"-home_default.jpg_onsat;";"mv ";EXTRAE(C2;1;22); "-large_default.jpg ";EXTRAE(C2;1;22);"-large_default.jpg_onsat;";"mv ";EXTRAE(C2;1;22); "-medium_default.jpg ";EXTRAE(C2;1;22);"-medium_default.jpg_onsat;";"mv ";EXTRAE(C2;1;22); "-small_default.jpg ";EXTRAE(C2;1;22);"-small_default.jpg_onsat;";"mv ";EXTRAE(C2;1;22); "-thickbox_default.jpg ";EXTRAE(C2;1;22);"-thickbox_default.jpg_onsat;")

=CONCATENAR("UPDATE ps_image set id_product = id_product*1000 WHERE id_image=";B2;" and id_product <100.000; ";"UPDATE ps_image_lang set id_image = id_image*1000 WHERE id_image=";B2;"; ";"UPDATE ps_image_shop set id_product = id_product*1000 WHERE id_image=";B2;" and id_product <100.000; ")

https://metrodyn.fr/2018/03/19/use-the-default-product-image-as-cover-image-in-prestashop/

UPDATE ps_image SET cover = NULL;
UPDATE ps_image_shop SET cover = NULL;

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

UPDATE IGNORE `ps_image` SET `cover` = 1;
UPDATE IGNORE `ps_image_shop` SET `cover` = 1;

http://felicianoborrego.com/combinaciones-productos-sql-exportar-prestashop/

SELECT pat.id_product
    , prd.reference
    , GROUP_CONCAT(DISTINCT(concat(atgl.name, ":", atg.group_type, ":", atg.position)) SEPARATOR ", ")
    , GROUP_CONCAT(DISTINCT(concat(pal.name, ":", atg.position)) SEPARATOR ", ")
    , pat.supplier_reference
    , pat.reference
 FROM ps_product_attribute pat
INNER JOIN ps_product prd ON (prd.id_product = pat.id_product)
 LEFT JOIN ps_product_attribute_combination pac ON (pat.id_product_attribute = pac.id_product_attribute)
 LEFT JOIN ps_attribute att ON (pac.id_attribute = att.id_attribute)
 LEFT JOIN ps_attribute_group atg ON ( atg.id_attribute_group = att.id_attribute_group)
 LEFT JOIN ps_attribute_group_lang atgl ON ( atgl.id_attribute_group = atg.id_attribute_group)
 LEFT JOIN ps_attribute_lang pal ON (pac.id_attribute = pal.id_attribute AND pal.id_lang = atgl.id_lang)
WHERE atgl.id_lang = 1
GROUP BY pat.id_product_attribute
ORDER BY pat.id_product, pac.id_attribute

https://www.prestashop.com/forums/topic/313612-sql-stock-con-combinaciones/

https://www.prestashop.com/forums/topic/519187-exportar-productos-con-url-de-su-direcci%C3%B3n-web-sql-consulta/

SELECT CONCAT(  'http://midominio.com/', l.`iso_code` ,  '/', cl.`link_rewrite` ,  '/', pl.`link_rewrite` ,  '-', p.`id_product` ,  '.html' ) AS ProductURL
FROM  `ps_product` p
LEFT JOIN  `ps_product_lang` pl ON pl.`id_product` = p.`id_product` 
LEFT JOIN  `ps_lang` l ON l.`id_lang` = pl.`id_lang` 
LEFT JOIN  `ps_category_lang` cl ON cl.`id_category` = p.`id_category_default` 
WHERE pl.`id_lang` =1
Revisa que los prefijos de las tablas sean iguales a los que estas utilizando y cambiando el id_lang del final por el del lenguaje en el que quieras seleccionar los enlaces o editando el WHERE por ejemplo a 

WHERE p.`active` =1

https://www.webempresa.com/foro/administracion-prestashop/exportacion-masiva-mediante-consulta-sql

SELECT pat.id_product
    , prd.reference
    , GROUP_CONCAT(DISTINCT(concat(atgl.name, ":", atg.group_type, ":", atg.position)) SEPARATOR ", ")
    , GROUP_CONCAT(DISTINCT(concat(pal.name, ":", atg.position)) SEPARATOR ", ")
    , pat.supplier_reference
    , pat.reference
 FROM ps_product_attribute pat
INNER JOIN ps_product prd ON (prd.id_product = pat.id_product)
 LEFT JOIN ps_product_attribute_combination pac ON (pat.id_product_attribute = pac.id_product_attribute)
 LEFT JOIN ps_attribute att ON (pac.id_attribute = att.id_attribute)
 LEFT JOIN ps_attribute_group atg ON ( atg.id_attribute_group = att.id_attribute_group)
 LEFT JOIN ps_attribute_group_lang atgl ON ( atgl.id_attribute_group = atg.id_attribute_group)
 LEFT JOIN ps_attribute_lang pal ON (pac.id_attribute = pal.id_attribute AND pal.id_lang = atgl.id_lang)
WHERE atgl.id_lang = 1
GROUP BY pat.id_product_attribute
ORDER BY pat.id_product, pac.id_attribute