Obtener las URL de las imagenes de las combinaciones de los PRODUCTOS

[Francisco Sampo]

Buenas noches Comunidad! Espero que esten todos muy bien. Al final pude hacer funcionar la consulta SQL que está más abajo pero me falta poder configurarla para obtener las imágenes de las combinaciones de los PRODUCTOS. Con esto podre exportar y llevarlos a diferentes Tiendas como Mercado Libre, Wish Local, Personal Tiendas y otras que acepten poder cargar masivamente productos desde Planillas como son los CSV. La consulta SQL que funciona, recuerden que tienen que cambiar el MTO_ por el prefijo de la tienda que ustedes tengan. 

SELECT p.id_product, p.active, pl.name AS 'Name',
    GROUP_CONCAT(DISTINCT(CONCAT('https://',
    IFNULL(conf.value, 'DominioDelSitio'),
    '/img/',
    IF(CHAR_LENGTH(pi.id_image) >= 5, 
        CONCAT(
        SUBSTRING(pi.id_image, -5, 1),
        '/'),
        ''),
    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), '/'), ''),
    pi.id_image,
    '.jpg')) SEPARATOR ', ') AS 'Images (x,y,z...)',
    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)',
    0 AS 'Delete existing images (0 = No, 1 = Yes)',
    GROUP_CONCAT(DISTINCT(CONCAT((fl.name), ':', (fvl.value), ':0')) SEPARATOR ',') AS 'Feature (Name:Value:Position)',
    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 mto_product p
LEFT JOIN mto_product_lang pl ON(p.id_product = pl.id_product)
LEFT JOIN mto_category_product cp ON(p.id_product = cp.id_product)
LEFT JOIN mto_category_lang cl ON(cp.id_category = cl.id_category)
LEFT JOIN mto_specific_price pr ON(p.id_product = pr.id_product)
LEFT JOIN mto_product_tag pt ON(p.id_product = pt.id_product)
LEFT JOIN mto_tag t ON(pt.id_tag = t.id_tag)
LEFT JOIN mto_image pi ON(p.id_product = pi.id_product)
LEFT JOIN mto_manufacturer pm ON(p.id_manufacturer = pm.id_manufacturer)
LEFT JOIN mto_supplier ps ON(p.id_supplier = ps.id_supplier)
LEFT JOIN mto_configuration conf ON conf.name = 'mto_SHOP_DOMAIN'
LEFT JOIN mto_feature_product fp ON p.id_product = fp.id_product
LEFT JOIN mto_feature_lang fl ON fp.id_feature = fl.id_feature
LEFT JOIN mto_feature_value_lang fvl ON fp.id_feature_value = fvl.id_feature_value
LEFT JOIN mto_feature f ON fp.id_feature = f.id_feature
LEFT JOIN mto_feature_value fv ON fp.id_feature_value = fv.id_feature_value
LEFT JOIN mto_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

Tambien recuerden de cambiar el id_lang que yo lo tengo en 1 por el número que ustedes tengan.

Esta consulta genera las direcciones de las imágenes pero no se cual es el campo para que haga referencia a la imagen real, busca cualquier cosa.

 

Espero que puedan pulirla para que realmente se puedan obtener las imágenes de las COMBINACIONES.

Saludos a toda la comunidad.