SQL en prestashop para exportación de productos

BotBeta dijo:

Para agregar el detalle de las variaciones de los productos tales como talle y color, necesitarás hacer un "JOIN" con la tabla "ps_product_attribute_combination" y "ps_attribute_lang".

A continuación te dejo la consulta SQL que deberías correr:

SQL:

SELECT
...
GROUP_CONCAT(DISTINCT(al.name) SEPARATOR ',') AS 'Variations',
...
FROM ps_product p
...
LEFT JOIN ps_product_attribute_combination pac ON p.id_product = pac.id_product
LEFT JOIN ps_attribute_lang al ON pac.id_attribute = al.id_attribute
...
WHERE pl.id_lang = 1 AND cl.id_lang = 1
GROUP BY p.id_product;

Espero que esto ayude a solucionar tu problema. Por favor, no olvides hacer una copia de seguridad antes de realizar cualquier modificación en la base de datos.

Hacer clic para expandir...

cholemon dijo:

Identa la query original que compartió el usuario anterior.

Hacer clic para expandir...

SELECT 
    p.id_product, 
    p.active, 
    pl.name AS 'Name',
    GROUP_CONCAT(DISTINCT(cl.name) SEPARATOR ',') AS 'Categories (x,y,z...)', 
    p.price AS 'Price tax excluded or Price tax included', 
    p.id_tax_rules_group AS 'Tax rules ID', 
    p.wholesale_price AS 'Wholesale price', 
    p.on_sale AS 'On sale (0/1)',
    IF(pr.reduction_type = 'amount', pr.reduction, '') AS 'Discount amount',
    IF(pr.reduction_type = 'percentage', pr.reduction, '') AS 'Discount percent',
    pr.from AS 'Discount from (yyyy-mm-dd)',
    pr.to AS 'Discount to (yyyy-mm-dd)', 
    p.reference AS 'Reference #',
    p.supplier_reference AS 'Supplier reference #', 
    ps.name AS 'Supplier', 
    pm.name AS 'Manufacturer',
    p.ean13 AS 'EAN13',
    p.upc AS 'UPC',
    p.ecotax AS 'Ecotax',
    p.width AS 'Width', 
    p.height AS 'Height', 
    p.depth AS 'Depth',
    p.weight AS 'Weight',
    sa.quantity as 'Quantity', 
    p.minimal_quantity AS 'Minimal quantity', 
    ... 
FROM 
    ps_product p 
LEFT JOIN 
    ps_product_lang pl ON(p.id_product = pl.id_product) 
...
WHERE 
    pl.id_lang = 1 AND cl.id_lang = 1 
GROUP BY 
    p.id_product;

BotBeta dijo:

Para agregar el detalle de las variaciones de los productos tales como talle y color, necesitarás hacer un "JOIN" con la tabla "ps_product_attribute_combination" y "ps_attribute_lang".

A continuación te dejo la consulta SQL que deberías correr:

SQL:

SELECT
...
GROUP_CONCAT(DISTINCT(al.name) SEPARATOR ',') AS 'Variations',
...
FROM ps_product p
...
LEFT JOIN ps_product_attribute_combination pac ON p.id_product = pac.id_product
LEFT JOIN ps_attribute_lang al ON pac.id_attribute = al.id_attribute
...
WHERE pl.id_lang = 1 AND cl.id_lang = 1
GROUP BY p.id_product;

Espero que esto ayude a solucionar tu problema. Por favor, no olvides hacer una copia de seguridad antes de realizar cualquier modificación en la base de datos.

Hacer clic para expandir...

cesarss27 dijo:

No me funciona, ¿sería agregar una consulta solo con ese código o se lo sumo al que ya tenía?

Hacer clic para expandir...

...
LEFT JOIN ps_product_attribute_combination pac ON (p.id_product = pac.id_product)
LEFT JOIN ps_attribute_lang al ON (pac.id_attribute = al.id_attribute AND al.id_lang = 1)
...
GROUP_CONCAT(DISTINCT al.name SEPARATOR ',') AS 'Variations',
...

...
GROUP BY p.id_product;

BotBeta dijo:

Mis disculpas por el error anterior, el código SQL que mencioné deberías incorporarlo en tu consulta existente. La idea es agregar el JOIN necesario para traer la información que necesitas (talla, color) desde las tablas "ps_product_attribute_combination" y "ps_attribute_lang". Aquí te dejo una porción del código donde deberías incorporarlo:

SQL:

...
LEFT JOIN ps_product_attribute_combination pac ON (p.id_product = pac.id_product)
LEFT JOIN ps_attribute_lang al ON (pac.id_attribute = al.id_attribute AND al.id_lang = 1)
...
GROUP_CONCAT(DISTINCT al.name SEPARATOR ',') AS 'Variations',
...

...
GROUP BY p.id_product;

Recuerda adaptar el código según las necesidades específicas de tus datos. Verifica que estés utilizando los nombres de tabla y columna correctos en función de tu base de datos.

Hacer clic para expandir...

cesarss27 dijo:

Podrías mostrarme como quedaría como quedaría incorporado a mi consulta existente.

Hacer clic para expandir...

SELECT p.id_product,
...
LEFT JOIN ps_product_attribute_combination pac ON p.id_product = pac.id_product
LEFT JOIN ps_attribute_lang al ON pac.id_attribute = al.id_attribute AND al.id_lang = 1
GROUP_CONCAT(DISTINCT al.name SEPARATOR ',') AS 'Variations',
...
FROM ps_product p
...
WHERE pl.id_lang = 1 AND cl.id_lang = 1
GROUP BY p.id_product;

BotBeta dijo:

Claro, lo siento por el malentendido anterior. Para mejorar mi respuesta, incorporaré las variaciones a tu consulta existente. Así debería quedar:

SQL:

SELECT p.id_product,
...
LEFT JOIN ps_product_attribute_combination pac ON p.id_product = pac.id_product
LEFT JOIN ps_attribute_lang al ON pac.id_attribute = al.id_attribute AND al.id_lang = 1
GROUP_CONCAT(DISTINCT al.name SEPARATOR ',') AS 'Variations',
...
FROM ps_product p
...
WHERE pl.id_lang = 1 AND cl.id_lang = 1
GROUP BY p.id_product;

Donde los puntos suspensivos ("...") indican el código existente que no se ha modificado. Solo se añadieron las líneas "LEFT JOIN ps_product_attribute_combination pac..." y "GROUP_CONCAT(DISTINCT al.name SEPARATOR ',') AS 'Variations'" para acomodar la recogida de detalles de variaciones en la consulta.
Recuerda realizar una copia de seguridad antes de realizar cualquier modificación en la base de datos.

Hacer clic para expandir...

cesarss27 dijo:

SELECT p.id_product, p.active, pl.name AS 'Name',

LEFT JOIN ps_product_attribute_combination pac ON p.id_product = pac.id_product
LEFT JOIN ps_attribute_lang al ON pac.id_attribute = al.id_attribute AND al.id_lang = 1
GROUP_CONCAT(DISTINCT al.name SEPARATOR ',') AS 'Variations',

GROUP_CONCAT(DISTINCT(cl.name) SEPARATOR ',') AS 'Categories (x,y,z...)', p.price AS 'Price tax excluded or Price tax included', p.id_tax_rules_group AS 'Tax rules ID', p.wholesale_price AS 'Wholesale price', p.on_sale AS 'On sale (0/1)', IF(pr.reduction_type = 'amount', pr.reduction, '') AS 'Discount amount', IF(pr.reduction_type = 'percentage', pr.reduction, '') AS 'Discount percent', pr.from AS 'Discount from (yyyy-mm-dd)', pr.to AS 'Discount to (yyyy-mm-dd)', p.reference AS 'Reference #', p.supplier_reference AS 'Supplier reference #', ps.name AS 'Supplier', pm.name AS 'Manufacturer', p.ean13 AS 'EAN13', p.upc AS 'UPC', p.ecotax AS 'Ecotax', p.width AS 'Width', p.height AS 'Height', p.depth AS 'Depth', p.weight AS 'Weight', sa.quantity as 'Quantity', p.minimal_quantity AS 'Minimal quantity', 'both' AS 'Visibility', p.additional_shipping_cost AS 'Additional shipping cost', p.unity AS 'Unity', p.unit_price_ratio AS 'Unit price', pl.description_short AS 'Short description', pl.description AS 'Description', IF(t.name IS NOT NULL, GROUP_CONCAT(DISTINCT(t.name) SEPARATOR ','), '') AS 'Tags (x,y,z...)', pl.meta_title AS 'Meta title', pl.meta_keywords AS 'Meta keywords', pl.meta_description AS 'Meta description', pl.link_rewrite AS 'URL rewritten', pl.available_now AS 'Text when in stock', pl.available_later AS 'Text when backorder allowed', p.available_for_order AS 'Available for order (0 = No, 1 = Yes)', '' AS 'Product available date', p.date_add 'Product creation date', p.show_price AS 'Show price (0 = No, 1 = Yes)', CONCAT('https://', -- get the shop domain IFNULL(conf.value, 'undefined_domain'), -- the path to the pictures folder '/img/p/', -- now take all the digits separetly as MySQL doesn't support loops in SELECT statements -- assuming we have smaller image id than 100'000 IF(CHAR_LENGTH(pi.id_image) >= 5, -- if we have 5 digits for the image id CONCAT( -- take the first digit SUBSTRING(pi.id_image, -5, 1), -- add a slash '/'), ''), -- repeat for the next digits IF(CHAR_LENGTH(pi.id_image) >= 4, CONCAT(SUBSTRING(pi.id_image, -4, 1), '/'), ''), IF(CHAR_LENGTH(pi.id_image) >= 3, CONCAT(SUBSTRING(pi.id_image, -3, 1), '/'), ''), if(CHAR_LENGTH(pi.id_image) >= 2, CONCAT(SUBSTRING(pi.id_image, -2, 1), '/'), ''), IF(CHAR_LENGTH(pi.id_image) >= 1, CONCAT(SUBSTRING(pi.id_image, -1, 1), '/'), ''), -- add the image id pi.id_image, -- put the image extension '.jpg') as image_url, CONCAT('https://', -- get the shop domain IFNULL(conf.value, 'undefined_domain'), -- the path to the pictures folder '/img/p/', -- now take all the digits separetly as MySQL doesn't support loops in SELECT statements -- assuming we have smaller image id than 100'000 IF(CHAR_LENGTH(pi2.id_image) >= 5, -- if we have 5 digits for the image id CONCAT( -- take the first digit SUBSTRING(pi2.id_image, -5, 1), -- add a slash '/'), ''), -- repeat for the next digits IF(CHAR_LENGTH(pi2.id_image) >= 4, CONCAT(SUBSTRING(pi2.id_image, -4, 1), '/'), ''), IF(CHAR_LENGTH(pi2.id_image) >= 3, CONCAT(SUBSTRING(pi2.id_image, -3, 1), '/'), ''), if(CHAR_LENGTH(pi2.id_image) >= 2, CONCAT(SUBSTRING(pi2.id_image, -2, 1), '/'), ''), IF(CHAR_LENGTH(pi2.id_image) >= 1, CONCAT(SUBSTRING(pi2.id_image, -1, 1), '/'), ''), -- add the image id pi2.id_image, -- put the image extension '.jpg') as image_url2, 0 AS 'Delete existing images (0 = No, 1 = Yes)', GROUP_CONCAT(DISTINCT(CONCAT((fl.name), ':', (fvl.value), ':0')) SEPARATOR ',') AS 'Feature (Name:Valueosition)', p.online_only AS 'Available online only (0 = No, 1 = Yes)', p.condition AS 'Cond', 0 AS 'Customizable (0 = No, 1 = Yes)', 0 AS 'Uploadable files (0 = No, 1 = Yes)', 0 AS 'Text fields (0 = No, 1 = Yes)', p.out_of_stock as 'Out of stock', '1' AS 'ID', null AS 'Action when out of stock', null AS 'Depends on stock', null AS 'Warehouse' FROM ps_product p

LEFT JOIN ps_product_lang pl ON(p.id_product = pl.id_product)
LEFT JOIN ps_category_product cp ON(p.id_product = cp.id_product)
LEFT JOIN ps_category_lang cl ON(cp.id_category = cl.id_category)
LEFT JOIN ps_specific_price pr ON(p.id_product = pr.id_product)
LEFT JOIN ps_product_tag pt ON(p.id_product = pt.id_product)
LEFT JOIN ps_tag t ON(pt.id_tag = t.id_tag)
LEFT JOIN ps_image pi ON(p.id_product = pi.id_product and pi.cover = 1)
LEFT JOIN ps_image pi2 ON(p.id_product = pi2.id_product and pi2.position = 2)
LEFT JOIN ps_manufacturer pm ON(p.id_manufacturer = pm.id_manufacturer)
LEFT JOIN ps_supplier ps ON(p.id_supplier = ps.id_supplier)
LEFT JOIN ps_configuration conf ON conf.name = 'PS_SHOP_DOMAIN'
LEFT JOIN ps_feature_product fp ON p.id_product = fp.id_product
LEFT JOIN ps_feature_lang fl ON fp.id_feature = fl.id_feature LEFT JOIN ps_feature_value_lang fvl ON fp.id_feature_value = fvl.id_feature_value
LEFT JOIN ps_feature f ON fp.id_feature = f.id_feature
LEFT JOIN ps_feature_value fv ON fp.id_feature_value = fv.id_feature_value
LEFT JOIN ps_stock_available sa ON (p.id_product = sa.id_product)

FROM ps_product p

WHERE pl.id_lang = 1 AND cl.id_lang = 1
GROUP BY p.id_product;

¿Así estaría bien?

Hacer clic para expandir...

SELECT 
    p.id_product, 
    p.active, 
    pl.name AS 'Name', 
    ...
    GROUP_CONCAT(DISTINCT al.name SEPARATOR ',') AS 'Variations',
    ...
    '1' AS 'ID', 
    null AS 'Action when out of stock', 
    null AS 'Depends on stock', 
    null AS 'Warehouse' 
FROM 
    ps_product p 
LEFT JOIN
    ps_product_lang pl ON p.id_product = pl.id_product
...
LEFT JOIN 
    ps_product_attribute_combination pac ON p.id_product = pac.id_product
LEFT JOIN 
    ps_attribute_lang al ON pac.id_attribute = al.id_attribute AND al.id_lang = 1
...
WHERE 
    pl.id_lang = 1 AND cl.id_lang = 1 
GROUP BY 
    p.id_product;

BotBeta dijo:

Lamento la confusión. En tu última consulta SQL, pareces haber duplicado la línea "FROM ps_product p" y tu primera columna luego de "SELECT" tiene un error. Aquí te dejo cómo incorporar las variaciones en tu consulta:

SQL:

SELECT
    p.id_product,
    p.active,
    pl.name AS 'Name',
    ...
    GROUP_CONCAT(DISTINCT al.name SEPARATOR ',') AS 'Variations',
    ...
    '1' AS 'ID',
    null AS 'Action when out of stock',
    null AS 'Depends on stock',
    null AS 'Warehouse'
FROM
    ps_product p
LEFT JOIN
    ps_product_lang pl ON p.id_product = pl.id_product
...
LEFT JOIN
    ps_product_attribute_combination pac ON p.id_product = pac.id_product
LEFT JOIN
    ps_attribute_lang al ON pac.id_attribute = al.id_attribute AND al.id_lang = 1
...
WHERE
    pl.id_lang = 1 AND cl.id_lang = 1
GROUP BY
    p.id_product;

Los puntos suspensivos ("...") representan el resto de las columnas que quieres seleccionar y el resto de las uniones que tienes en tu consulta original. Donde ves 'Variations', deberías obtener los detalles de las variaciones (talla, color, etc.). Por favor, realiza una copia de seguridad antes de realizar cualquier modificación en la base de datos.

Hacer clic para expandir...

cesarss27 dijo:

Ok, entonces asi estaría bien:

SELECT
p.id_product,
p.active,
pl.name AS 'Name',

LEFT JOIN ps_product_attribute_combination pac ON p.id_product = pac.id_product
LEFT JOIN ps_attribute_lang al ON pac.id_attribute = al.id_attribute AND al.id_lang = 1
GROUP_CONCAT(DISTINCT al.name SEPARATOR ',') AS 'Variations',


GROUP_CONCAT(DISTINCT(cl.name) SEPARATOR ',') AS 'Categories (x,y,z...)', p.price AS 'Price tax excluded or Price tax included', p.id_tax_rules_group AS 'Tax rules ID', p.wholesale_price AS 'Wholesale price', p.on_sale AS 'On sale (0/1)', IF(pr.reduction_type = 'amount', pr.reduction, '') AS 'Discount amount', IF(pr.reduction_type = 'percentage', pr.reduction, '') AS 'Discount percent', pr.from AS 'Discount from (yyyy-mm-dd)', pr.to AS 'Discount to (yyyy-mm-dd)', p.reference AS 'Reference #', p.supplier_reference AS 'Supplier reference #', ps.name AS 'Supplier', pm.name AS 'Manufacturer', p.ean13 AS 'EAN13', p.upc AS 'UPC', p.ecotax AS 'Ecotax', p.width AS 'Width', p.height AS 'Height', p.depth AS 'Depth', p.weight AS 'Weight', sa.quantity as 'Quantity', p.minimal_quantity AS 'Minimal quantity', 'both' AS 'Visibility', p.additional_shipping_cost AS 'Additional shipping cost', p.unity AS 'Unity', p.unit_price_ratio AS 'Unit price', pl.description_short AS 'Short description', pl.description AS 'Description', IF(t.name IS NOT NULL, GROUP_CONCAT(DISTINCT(t.name) SEPARATOR ','), '') AS 'Tags (x,y,z...)', pl.meta_title AS 'Meta title', pl.meta_keywords AS 'Meta keywords', pl.meta_description AS 'Meta description', pl.link_rewrite AS 'URL rewritten', pl.available_now AS 'Text when in stock', pl.available_later AS 'Text when backorder allowed', p.available_for_order AS 'Available for order (0 = No, 1 = Yes)', '' AS 'Product available date', p.date_add 'Product creation date', p.show_price AS 'Show price (0 = No, 1 = Yes)', CONCAT('https://', -- get the shop domain IFNULL(conf.value, 'undefined_domain'), -- the path to the pictures folder '/img/p/', -- now take all the digits separetly as MySQL doesn't support loops in SELECT statements -- assuming we have smaller image id than 100'000 IF(CHAR_LENGTH(pi.id_image) >= 5, -- if we have 5 digits for the image id CONCAT( -- take the first digit SUBSTRING(pi.id_image, -5, 1), -- add a slash '/'), ''), -- repeat for the next digits IF(CHAR_LENGTH(pi.id_image) >= 4, CONCAT(SUBSTRING(pi.id_image, -4, 1), '/'), ''), IF(CHAR_LENGTH(pi.id_image) >= 3, CONCAT(SUBSTRING(pi.id_image, -3, 1), '/'), ''), if(CHAR_LENGTH(pi.id_image) >= 2, CONCAT(SUBSTRING(pi.id_image, -2, 1), '/'), ''), IF(CHAR_LENGTH(pi.id_image) >= 1, CONCAT(SUBSTRING(pi.id_image, -1, 1), '/'), ''), -- add the image id pi.id_image, -- put the image extension '.jpg') as image_url, CONCAT('https://', -- get the shop domain IFNULL(conf.value, 'undefined_domain'), -- the path to the pictures folder '/img/p/', -- now take all the digits separetly as MySQL doesn't support loops in SELECT statements -- assuming we have smaller image id than 100'000 IF(CHAR_LENGTH(pi2.id_image) >= 5, -- if we have 5 digits for the image id CONCAT( -- take the first digit SUBSTRING(pi2.id_image, -5, 1), -- add a slash '/'), ''), -- repeat for the next digits IF(CHAR_LENGTH(pi2.id_image) >= 4, CONCAT(SUBSTRING(pi2.id_image, -4, 1), '/'), ''), IF(CHAR_LENGTH(pi2.id_image) >= 3, CONCAT(SUBSTRING(pi2.id_image, -3, 1), '/'), ''), if(CHAR_LENGTH(pi2.id_image) >= 2, CONCAT(SUBSTRING(pi2.id_image, -2, 1), '/'), ''), IF(CHAR_LENGTH(pi2.id_image) >= 1, CONCAT(SUBSTRING(pi2.id_image, -1, 1), '/'), ''), -- add the image id pi2.id_image, -- put the image extension '.jpg') as image_url2, 0 AS 'Delete existing images (0 = No, 1 = Yes)', GROUP_CONCAT(DISTINCT(CONCAT((fl.name), ':', (fvl.value), ':0')) SEPARATOR ',') AS 'Feature (Name:Valueosition)', p.online_only AS 'Available online only (0 = No, 1 = Yes)', p.condition AS 'Cond', 0 AS 'Customizable (0 = No, 1 = Yes)', 0 AS 'Uploadable files (0 = No, 1 = Yes)', 0 AS 'Text fields (0 = No, 1 = Yes)', p.out_of_stock as 'Out of stock',

'1' AS 'ID',
null AS 'Action when out of stock',
null AS 'Depends on stock',
null AS 'Warehouse'

FROM ps_product p

LEFT JOIN ps_product_lang pl ON(p.id_product = pl.id_product)
LEFT JOIN ps_category_product cp ON(p.id_product = cp.id_product)
LEFT JOIN ps_category_lang cl ON(cp.id_category = cl.id_category)
LEFT JOIN ps_specific_price pr ON(p.id_product = pr.id_product)
LEFT JOIN ps_product_tag pt ON(p.id_product = pt.id_product)
LEFT JOIN ps_tag t ON(pt.id_tag = t.id_tag)
LEFT JOIN ps_image pi ON(p.id_product = pi.id_product and pi.cover = 1)
LEFT JOIN ps_image pi2 ON(p.id_product = pi2.id_product and pi2.position = 2)
LEFT JOIN ps_manufacturer pm ON(p.id_manufacturer = pm.id_manufacturer)
LEFT JOIN ps_supplier ps ON(p.id_supplier = ps.id_supplier)
LEFT JOIN ps_configuration conf ON conf.name = 'PS_SHOP_DOMAIN'
LEFT JOIN ps_feature_product fp ON p.id_product = fp.id_product
LEFT JOIN ps_feature_lang fl ON fp.id_feature = fl.id_feature LEFT JOIN ps_feature_value_lang fvl ON fp.id_feature_value = fvl.id_feature_value
LEFT JOIN ps_feature f ON fp.id_feature = f.id_feature
LEFT JOIN ps_feature_value fv ON fp.id_feature_value = fv.id_feature_value
LEFT JOIN ps_stock_available sa ON (p.id_product = sa.id_product)



WHERE pl.id_lang = 1 AND cl.id_lang = 1
GROUP BY p.id_product;

Hacer clic para expandir...

BotBeta dijo:

Lo siento, no pude comunicarme con el grupo de expertos ChatGPT.

Hacer clic para expandir...

cesarss27 dijo:

te pregunto de nuevo entonces, ¿así estaría bien?

SELECT
p.id_product,
p.active,
pl.name AS 'Name',

LEFT JOIN ps_product_attribute_combination pac ON p.id_product = pac.id_product
LEFT JOIN ps_attribute_lang al ON pac.id_attribute = al.id_attribute AND al.id_lang = 1
GROUP_CONCAT(DISTINCT al.name SEPARATOR ',') AS 'Variations',


GROUP_CONCAT(DISTINCT(cl.name) SEPARATOR ',') AS 'Categories (x,y,z...)', p.price AS 'Price tax excluded or Price tax included', p.id_tax_rules_group AS 'Tax rules ID', p.wholesale_price AS 'Wholesale price', p.on_sale AS 'On sale (0/1)', IF(pr.reduction_type = 'amount', pr.reduction, '') AS 'Discount amount', IF(pr.reduction_type = 'percentage', pr.reduction, '') AS 'Discount percent', pr.from AS 'Discount from (yyyy-mm-dd)', pr.to AS 'Discount to (yyyy-mm-dd)', p.reference AS 'Reference #', p.supplier_reference AS 'Supplier reference #', ps.name AS 'Supplier', pm.name AS 'Manufacturer', p.ean13 AS 'EAN13', p.upc AS 'UPC', p.ecotax AS 'Ecotax', p.width AS 'Width', p.height AS 'Height', p.depth AS 'Depth', p.weight AS 'Weight', sa.quantity as 'Quantity', p.minimal_quantity AS 'Minimal quantity', 'both' AS 'Visibility', p.additional_shipping_cost AS 'Additional shipping cost', p.unity AS 'Unity', p.unit_price_ratio AS 'Unit price', pl.description_short AS 'Short description', pl.description AS 'Description', IF(t.name IS NOT NULL, GROUP_CONCAT(DISTINCT(t.name) SEPARATOR ','), '') AS 'Tags (x,y,z...)', pl.meta_title AS 'Meta title', pl.meta_keywords AS 'Meta keywords', pl.meta_description AS 'Meta description', pl.link_rewrite AS 'URL rewritten', pl.available_now AS 'Text when in stock', pl.available_later AS 'Text when backorder allowed', p.available_for_order AS 'Available for order (0 = No, 1 = Yes)', '' AS 'Product available date', p.date_add 'Product creation date', p.show_price AS 'Show price (0 = No, 1 = Yes)', CONCAT('https://', -- get the shop domain IFNULL(conf.value, 'undefined_domain'), -- the path to the pictures folder '/img/p/', -- now take all the digits separetly as MySQL doesn't support loops in SELECT statements -- assuming we have smaller image id than 100'000 IF(CHAR_LENGTH(pi.id_image) >= 5, -- if we have 5 digits for the image id CONCAT( -- take the first digit SUBSTRING(pi.id_image, -5, 1), -- add a slash '/'), ''), -- repeat for the next digits IF(CHAR_LENGTH(pi.id_image) >= 4, CONCAT(SUBSTRING(pi.id_image, -4, 1), '/'), ''), IF(CHAR_LENGTH(pi.id_image) >= 3, CONCAT(SUBSTRING(pi.id_image, -3, 1), '/'), ''), if(CHAR_LENGTH(pi.id_image) >= 2, CONCAT(SUBSTRING(pi.id_image, -2, 1), '/'), ''), IF(CHAR_LENGTH(pi.id_image) >= 1, CONCAT(SUBSTRING(pi.id_image, -1, 1), '/'), ''), -- add the image id pi.id_image, -- put the image extension '.jpg') as image_url, CONCAT('https://', -- get the shop domain IFNULL(conf.value, 'undefined_domain'), -- the path to the pictures folder '/img/p/', -- now take all the digits separetly as MySQL doesn't support loops in SELECT statements -- assuming we have smaller image id than 100'000 IF(CHAR_LENGTH(pi2.id_image) >= 5, -- if we have 5 digits for the image id CONCAT( -- take the first digit SUBSTRING(pi2.id_image, -5, 1), -- add a slash '/'), ''), -- repeat for the next digits IF(CHAR_LENGTH(pi2.id_image) >= 4, CONCAT(SUBSTRING(pi2.id_image, -4, 1), '/'), ''), IF(CHAR_LENGTH(pi2.id_image) >= 3, CONCAT(SUBSTRING(pi2.id_image, -3, 1), '/'), ''), if(CHAR_LENGTH(pi2.id_image) >= 2, CONCAT(SUBSTRING(pi2.id_image, -2, 1), '/'), ''), IF(CHAR_LENGTH(pi2.id_image) >= 1, CONCAT(SUBSTRING(pi2.id_image, -1, 1), '/'), ''), -- add the image id pi2.id_image, -- put the image extension '.jpg') as image_url2, 0 AS 'Delete existing images (0 = No, 1 = Yes)', GROUP_CONCAT(DISTINCT(CONCAT((fl.name), ':', (fvl.value), ':0')) SEPARATOR ',') AS 'Feature (Name:Valueosition)', p.online_only AS 'Available online only (0 = No, 1 = Yes)', p.condition AS 'Cond', 0 AS 'Customizable (0 = No, 1 = Yes)', 0 AS 'Uploadable files (0 = No, 1 = Yes)', 0 AS 'Text fields (0 = No, 1 = Yes)', p.out_of_stock as 'Out of stock',

'1' COMO 'ID',
null AS 'Acción cuando no hay existencias',
null AS 'Depende del stock',
null AS 'Almacén'

DE ps_producto p

UNIRSE A LA IZQUIERDA ps_product_lang pl ON(p.id_product = pl.id_product)
UNIRSE A LA IZQUIERDA ps_category_product cp ON(p.id_product = cp.id_product)
UNIRSE A LA IZQUIERDA ps_category_lang cl ON(cp.id_category = cl.id_category)
UNIRSE A LA IZQUIERDA ps_specific_price pr ON(p.id_product = pr.id_product)
UNIRSE A LA IZQUIERDA ps_product_tag pt ON(p.id_product = pt.id_product)
UNIRSE A LA IZQUIERDA ps_tag t ON(pt.id_tag = t.id_tag)
UNIRSE A LA IZQUIERDA ps_image pi ON(p.id_product = pi.id_product y pi.cover = 1)
UNIRSE A LA IZQUIERDA ps_image pi2 ON(p.id_product = pi2.id_product y pi2.position = 2)
UNIRSE A LA IZQUIERDA ps_manufacturer pm ON(p.id_manufacturer = pm.id_manufacturer)
UNIRSE A LA IZQUIERDA ps_supplier ps ON(p.id_supplier = ps.id_supplier)
UNIRSE IZQUIERDO ps_configuration conf ON conf.name = 'PS_SHOP_DOMAIN'
UNIRSE IZQUIERDO ps_feature_product fp ON p.id_product = fp.id_product
UNIRSE A LA IZQUIERDA ps_feature_lang fl ON fp.id_feature = fl.id_feature UNIRSE A LA IZQUIERDA ps_feature_value_lang fvl ON fp.id_feature_value = fvl.id_feature_value
UNIRSE A LA IZQUIERDA ps_feature f ENCENDIDO fp.id_feature = f.id_feature
UNIRSE A LA IZQUIERDA ps_feature_value fv ON fp.id_feature_value = fv.id_feature_value
UNIRSE A LA IZQUIERDA ps_stock_available sa ON (p.id_product = sa.id_product)



DONDE pl.id_lang = 1 Y cl.id_lang = 1
GRUPO POR p.id_product;

Hacer clic para expandir...

BotBeta dijo:

Sorry I couldn't contact the ChatGPT think tank

Hacer clic para expandir...

cesarss27 dijo:

te pregunto de nuevo entonces, ¿así estaría bien?

SELECT
p.id_product,
p.active,
pl.name AS 'Name',

LEFT JOIN ps_product_attribute_combination pac ON p.id_product = pac.id_product
LEFT JOIN ps_attribute_lang al ON pac.id_attribute = al.id_attribute AND al.id_lang = 1
GROUP_CONCAT(DISTINCT al.name SEPARATOR ',') AS 'Variations',


GROUP_CONCAT(DISTINCT(cl.name) SEPARATOR ',') AS 'Categories (x,y,z...)', p.price AS 'Price tax excluded or Price tax included', p.id_tax_rules_group AS 'Tax rules ID', p.wholesale_price AS 'Wholesale price', p.on_sale AS 'On sale (0/1)', IF(pr.reduction_type = 'amount', pr.reduction, '') AS 'Discount amount', IF(pr.reduction_type = 'percentage', pr.reduction, '') AS 'Discount percent', pr.from AS 'Discount from (yyyy-mm-dd)', pr.to AS 'Discount to (yyyy-mm-dd)', p.reference AS 'Reference #', p.supplier_reference AS 'Supplier reference #', ps.name AS 'Supplier', pm.name AS 'Manufacturer', p.ean13 AS 'EAN13', p.upc AS 'UPC', p.ecotax AS 'Ecotax', p.width AS 'Width', p.height AS 'Height', p.depth AS 'Depth', p.weight AS 'Weight', sa.quantity as 'Quantity', p.minimal_quantity AS 'Minimal quantity', 'both' AS 'Visibility', p.additional_shipping_cost AS 'Additional shipping cost', p.unity AS 'Unity', p.unit_price_ratio AS 'Unit price', pl.description_short AS 'Short description', pl.description AS 'Description', IF(t.name IS NOT NULL, GROUP_CONCAT(DISTINCT(t.name) SEPARATOR ','), '') AS 'Tags (x,y,z...)', pl.meta_title AS 'Meta title', pl.meta_keywords AS 'Meta keywords', pl.meta_description AS 'Meta description', pl.link_rewrite AS 'URL rewritten', pl.available_now AS 'Text when in stock', pl.available_later AS 'Text when backorder allowed', p.available_for_order AS 'Available for order (0 = No, 1 = Yes)', '' AS 'Product available date', p.date_add 'Product creation date', p.show_price AS 'Show price (0 = No, 1 = Yes)', CONCAT('https://', -- get the shop domain IFNULL(conf.value, 'undefined_domain'), -- the path to the pictures folder '/img/p/', -- now take all the digits separetly as MySQL doesn't support loops in SELECT statements -- assuming we have smaller image id than 100'000 IF(CHAR_LENGTH(pi.id_image) >= 5, -- if we have 5 digits for the image id CONCAT( -- take the first digit SUBSTRING(pi.id_image, -5, 1), -- add a slash '/'), ''), -- repeat for the next digits IF(CHAR_LENGTH(pi.id_image) >= 4, CONCAT(SUBSTRING(pi.id_image, -4, 1), '/'), ''), IF(CHAR_LENGTH(pi.id_image) >= 3, CONCAT(SUBSTRING(pi.id_image, -3, 1), '/'), ''), if(CHAR_LENGTH(pi.id_image) >= 2, CONCAT(SUBSTRING(pi.id_image, -2, 1), '/'), ''), IF(CHAR_LENGTH(pi.id_image) >= 1, CONCAT(SUBSTRING(pi.id_image, -1, 1), '/'), ''), -- add the image id pi.id_image, -- put the image extension '.jpg') as image_url, CONCAT('https://', -- get the shop domain IFNULL(conf.value, 'undefined_domain'), -- the path to the pictures folder '/img/p/', -- now take all the digits separetly as MySQL doesn't support loops in SELECT statements -- assuming we have smaller image id than 100'000 IF(CHAR_LENGTH(pi2.id_image) >= 5, -- if we have 5 digits for the image id CONCAT( -- take the first digit SUBSTRING(pi2.id_image, -5, 1), -- add a slash '/'), ''), -- repeat for the next digits IF(CHAR_LENGTH(pi2.id_image) >= 4, CONCAT(SUBSTRING(pi2.id_image, -4, 1), '/'), ''), IF(CHAR_LENGTH(pi2.id_image) >= 3, CONCAT(SUBSTRING(pi2.id_image, -3, 1), '/'), ''), if(CHAR_LENGTH(pi2.id_image) >= 2, CONCAT(SUBSTRING(pi2.id_image, -2, 1), '/'), ''), IF(CHAR_LENGTH(pi2.id_image) >= 1, CONCAT(SUBSTRING(pi2.id_image, -1, 1), '/'), ''), -- add the image id pi2.id_image, -- put the image extension '.jpg') as image_url2, 0 AS 'Delete existing images (0 = No, 1 = Yes)', GROUP_CONCAT(DISTINCT(CONCAT((fl.name), ':', (fvl.value), ':0')) SEPARATOR ',') AS 'Feature (Name:Valueosition)', p.online_only AS 'Available online only (0 = No, 1 = Yes)', p.condition AS 'Cond', 0 AS 'Customizable (0 = No, 1 = Yes)', 0 AS 'Uploadable files (0 = No, 1 = Yes)', 0 AS 'Text fields (0 = No, 1 = Yes)', p.out_of_stock as 'Out of stock',

'1' COMO 'ID',
null AS 'Acción cuando no hay existencias',
null AS 'Depende del stock',
null AS 'Almacén'

DE ps_producto p

UNIRSE A LA IZQUIERDA ps_product_lang pl ON(p.id_product = pl.id_product)
UNIRSE A LA IZQUIERDA ps_category_product cp ON(p.id_product = cp.id_product)
UNIRSE A LA IZQUIERDA ps_category_lang cl ON(cp.id_category = cl.id_category)
UNIRSE A LA IZQUIERDA ps_specific_price pr ON(p.id_product = pr.id_product)
UNIRSE A LA IZQUIERDA ps_product_tag pt ON(p.id_product = pt.id_product)
UNIRSE A LA IZQUIERDA ps_tag t ON(pt.id_tag = t.id_tag)
UNIRSE A LA IZQUIERDA ps_image pi ON(p.id_product = pi.id_product y pi.cover = 1)
UNIRSE A LA IZQUIERDA ps_image pi2 ON(p.id_product = pi2.id_product y pi2.position = 2)
UNIRSE A LA IZQUIERDA ps_manufacturer pm ON(p.id_manufacturer = pm.id_manufacturer)
UNIRSE A LA IZQUIERDA ps_supplier ps ON(p.id_supplier = ps.id_supplier)
UNIRSE IZQUIERDO ps_configuration conf ON conf.name = 'PS_SHOP_DOMAIN'
UNIRSE IZQUIERDO ps_feature_product fp ON p.id_product = fp.id_product
UNIRSE A LA IZQUIERDA ps_feature_lang fl ON fp.id_feature = fl.id_feature UNIRSE A LA IZQUIERDA ps_feature_value_lang fvl ON fp.id_feature_value = fvl.id_feature_value
UNIRSE A LA IZQUIERDA ps_feature f ENCENDIDO fp.id_feature = f.id_feature
UNIRSE A LA IZQUIERDA ps_feature_value fv ON fp.id_feature_value = fv.id_feature_value
UNIRSE A LA IZQUIERDA ps_stock_available sa ON (p.id_product = sa.id_product)



DONDE pl.id_lang = 1 Y cl.id_lang = 1
GRUPO POR p.id_product;

Hacer clic para expandir...