I need to add the images of each combination to this SQL query

[Jocafri]

Hello, I have this SQL query that I need to add to to extract the images corresponding to each combination.

Quote

SELECT m.name AS manufacturer, p.id_product, pl.name, IFNULL(pa.reference, p.reference)  'Reference',
GROUP_CONCAT(DISTINCT(al.name) SEPARATOR ", ") AS combinations,

GROUP_CONCAT(DISTINCT(cl.name) SEPARATOR ",") AS categories, p.price, pa.price, p.id_tax_rules_group, p.wholesale_price,
p.reference, p.supplier_reference, p.id_supplier, p.id_manufacturer, p.upc, p.ecotax, p.weight, s.quantity,
pl.description_short, pl.description, pl.meta_title, pl.meta_keywords, pl.meta_description, pl.link_rewrite,
pl.available_now, pl.available_later, p.available_for_order, p.date_add, p.show_price, p.online_only, p.condition,
p.id_shop_default
FROM ps_product p
LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product)
LEFT JOIN ps_manufacturer m ON (p.id_manufacturer = m.id_manufacturer)
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_category c ON (cp.id_category = c.id_category)
LEFT JOIN ps_stock_available s ON (p.id_product = s.id_product)
LEFT JOIN ps_product_tag pt ON (p.id_product = pt.id_product)
LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product)
LEFT JOIN ps_product_attribute_combination pac ON (pac.id_product_attribute = pa.id_product_attribute)
LEFT JOIN ps_attribute_lang al ON (al.id_attribute = pac.id_attribute)
WHERE pl.id_lang = 1
AND cl.id_lang = 1
AND p.id_shop_default = 1
AND c.id_shop_default = 1
GROUP BY pac.id_product_attribute

Hope someone can help me

Thanks!

[AddWeb Solution]

Hi,

To add the images corresponding to each combination, you can further extend your SQL query to include the image information. Assuming you are using the ps_image and ps_product_attribute_image tables to store the image data, you can use LEFT JOIN to include the images in your query.

p.id_shop_default,

GROUP_CONCAT(DISTINCT i.`id_image`) AS images_ids,

GROUP_CONCAT(DISTINCT il.`legend` SEPARATOR "|") AS image_legends

....

....

LEFT JOIN ps_attribute_lang al ON (al.id_attribute = pac.id_attribute)

LEFT JOIN ps_product_attribute_image pai ON (pai.id_product_attribute = pa.id_product_attribute)

LEFT JOIN ps_image i ON (i.id_image = pai.id_image

LEFT JOIN ps_image_lang il ON (i.id_image = il.id_image AND il.id_lang = 1)

WHERE pl.id_lang = 1

....

....

Kindly add cup or like If this helps!

Thanks!

[AddWeb Solution]

@Jocafri  The above code does attempt to include images in the query, but it may not give you the desired result for displaying images for each combination.

To extract the images for each combination, you need to adjust the query to fetch the image URLs for each product combination.

p.id_shop_default,
    GROUP_CONCAT(DISTINCT CONCAT('image_url:', i.`id_image`) SEPARATOR '|') AS images -- Concatenates the image URLs with prefix
FROM ps_product p
.....
.....
LEFT JOIN ps_attribute_lang al ON (al.id_attribute = pac.id_attribute)
LEFT JOIN ps_product_attribute_image pai ON (pai.id_product_attribute = pa.id_product_attribute) -- Joining combination image table
LEFT JOIN ps_image i ON (i.id_image = pai.id_image) -- Joining image table
LEFT JOIN ps_image_lang il ON (i.id_image = il.id_image AND il.id_lang = 1) -- Joining image lang table
WHERE pl.id_lang = 1
.....
GROUP BY pac.id_product_attribute;

Each concatenated string contains the prefix 'image_url:' followed by the image ID.

After running this query, you can parse the result in your application code and extract the image URLs for each combination by splitting the 'images' field using the '|' character as a delimiter and then further extracting the image URLs from the substrings containing 'image_url:'. This way, you'll have the image URLs corresponding to each combination.

Thanks!