Query getting url product images

[monicaamb]

Hello, I have a query to obtain the links of all the images of my products in PrestaShop, however, the images appear duplicated.

Is there a JOIN that has an additional condition to prevent duplicates from appearing?

I am going to share my query:

SELECT
p.active 'Active',
m.name 'Manufacturer',
p.id_product 'Product number',
p.reference 'Reference',
pl.name 'Product name',
GROUP_CONCAT(DISTINCT(al.name) SEPARATOR ", ") AS 'Combination',
s.quantity 'Quantity',
p.price 'Price w/o VAT',
pa.price 'Combination price',
p.wholesale_price 'Wholesale price',
GROUP_CONCAT(DISTINCT(cl.name) SEPARATOR ",") AS 'Product groups',
p.weight 'Weight',
p.id_tax_rules_group 'TAX group',
pa.reference 'Combination reference',
pl.description_short 'Short description',
pl.description 'Long description',
pl.meta_title 'Meta Title',
pl.meta_keywords 'Meta Keywords',
pl.meta_description 'Meta Description',
pl.link_rewrite 'Link',
pl.available_now 'In stock text',
pl.available_later 'Coming text',
p.available_for_order 'Orderable text',
p.date_add 'Added',
p.show_price 'Show price',
p.online_only 'Only online',
GROUP_CONCAT("http://", su.domain, su.physical_uri , IF(pai.id_image IS NOT NULL,pai.id_image,im.id_image), "/", REPLACE(REPLACE(REPLACE(pl.name," ","_"),"\"",""),"#",""), ".jpg") as "Image URL"

FROM
w5kn_product p
LEFT JOIN w5kn_product_lang pl ON (p.id_product = pl.id_product and pl.id_lang=2)
LEFT JOIN w5kn_manufacturer m ON (p.id_manufacturer = m.id_manufacturer)
LEFT JOIN w5kn_category_product cp ON (p.id_product = cp.id_product)
LEFT JOIN w5kn_category c ON (cp.id_category = c.id_category)
LEFT JOIN w5kn_category_lang cl ON (cp.id_category = cl.id_category and cl.id_lang=2)
LEFT JOIN w5kn_product_attribute pa ON (p.id_product = pa.id_product)
LEFT JOIN w5kn_stock_available s ON (p.id_product = s.id_product and (pa.id_product_attribute=s.id_product_attribute or pa.id_product_attribute is null))
LEFT JOIN w5kn_product_tag pt ON (p.id_product = pt.id_product)
LEFT JOIN w5kn_product_attribute_combination pac ON (pac.id_product_attribute = pa.id_product_attribute)
LEFT JOIN w5kn_attribute_lang al ON (al.id_attribute = pac.id_attribute and al.id_lang=2)
LEFT JOIN w5kn_shop sh ON p.id_shop_default = sh.id_shop 
LEFT JOIN w5kn_shop_url su ON su.id_shop = sh.id_shop AND su.main = 1

LEFT JOIN w5kn_image im ON (p.id_product = im.id_product)

LEFT JOIN w5kn_product_attribute_image pai ON (pai.id_product_attribute = s.id_product_attribute)
GROUP BY p.id_product,pac.id_product_attribute order by p.id_product

 

[endriu107]

You have it in code:

LEFT JOIN w5kn_image im ON (p.id_product = im.id_product) - first time

LEFT JOIN w5kn_product_attribute_image pai ON (pai.id_product_attribute = s.id_product_attribute) - second time

[monicaamb]

On 3/15/2023 at 4:44 PM, endriu107 said:

You have it in code:

LEFT JOIN w5kn_image im ON (p.id_product = im.id_product) - first time

LEFT JOIN w5kn_product_attribute_image pai ON (pai.id_product_attribute = s.id_product_attribute) - second time

Thank you but I obtain an error if I try to delete any of these lines:

[Mian Waqas]

To avoid duplicate image URLs, you can add a DISTINCT clause to your GROUP_CONCAT function. Here's an updated version of your query with the DISTINCT clause added:

SELECT
p.active 'Active',
m.name 'Manufacturer',
p.id_product 'Product number',
p.reference 'Reference',
pl.name 'Product name',
GROUP_CONCAT(DISTINCT al.name SEPARATOR ", ") AS 'Combination',
s.quantity 'Quantity',
p.price 'Price w/o VAT',
pa.price 'Combination price',
p.wholesale_price 'Wholesale price',
GROUP_CONCAT(DISTINCT cl.name SEPARATOR ",") AS 'Product groups',
p.weight 'Weight',
p.id_tax_rules_group 'TAX group',
pa.reference 'Combination reference',
pl.description_short 'Short description',
pl.description 'Long description',
pl.meta_title 'Meta Title',
pl.meta_keywords 'Meta Keywords',
pl.meta_description 'Meta Description',
pl.link_rewrite 'Link',
pl.available_now 'In stock text',
pl.available_later 'Coming text',
p.available_for_order 'Orderable text',
p.date_add 'Added',
p.show_price 'Show price',
p.online_only 'Only online',
GROUP_CONCAT(DISTINCT "http://", su.domain, su.physical_uri , IF(pai.id_image IS NOT NULL,pai.id_image,im.id_image), "/", REPLACE(REPLACE(REPLACE(pl.name," ","_"),"\"",""),"#",""), ".jpg") as "Image URL"
FROM
w5kn_product p
LEFT JOIN w5kn_product_lang pl ON (p.id_product = pl.id_product and pl.id_lang=2)
LEFT JOIN w5kn_manufacturer m ON (p.id_manufacturer = m.id_manufacturer)
LEFT JOIN w5kn_category_product cp ON (p.id_product = cp.id_product)
LEFT JOIN w5kn_category c ON (cp.id_category = c.id_category)
LEFT JOIN w5kn_category_lang cl ON (cp.id_category = cl.id_category and cl.id_lang=2)
LEFT JOIN w5kn_product_attribute pa ON (p.id_product = pa.id_product)
LEFT JOIN w5kn_stock_available s ON (p.id_product = s.id_product and (pa.id_product_attribute=s.id_product_attribute or pa.id_product_attribute is null))
LEFT JOIN w5kn_product_tag pt ON (p.id_product = pt.id_product)
LEFT JOIN w5kn_product_attribute_combination pac ON (pac.id_product_attribute = pa.id_product_attribute)
LEFT JOIN w5kn_attribute_lang al ON (al.id_attribute = pac.id_attribute and al.id_lang=2)
LEFT JOIN w5kn_shop sh ON p.id_shop_default = sh.id_shop 
LEFT JOIN w5kn_shop_url su ON su.id_shop = sh.id_shop AND su.main = 1
LEFT JOIN w5kn_image im ON (p.id_product = im.id_product)
LEFT JOIN w5kn_product_attribute_image pai ON (pai.id_product_attribute = s.id_product_attribute)
GROUP BY p.id_product,pac.id_product_attribute ORDER BY p.id_product

Note the DISTINCT clause added to the GROUP_CONCAT functions for al.name and "http://", su.domain, su.physical_uri , IF(pai.id_image IS NOT NULL,pai.id_image,im.id_image), "/", REPLACE(REPLACE(REPLACE(pl.name," ","_"),"\"",""),"#",""), ".jpg" to remove duplicate values.