another version with all combinaitions of attributes with all lang.
For me in my database, I made a view to have combinations of attributes, it's simpler after to have it in SQL queries
* Warning to prefixes.
In this version the attributes exit is in the expected order
SELECT
m.name AS manufacturer,
p.id_product,
cm.id_product_attribute,
pl.name,
coalesce(pa.reference, p.reference) as reference,
p.wholesale_price, s.quantity,
cm.combinaison AS ATTRIBUTE
FROM product p
LEFT JOIN product_lang pl ON (p.id_product = pl.id_product)
LEFT JOIN product_attribute pa ON (p.id_product = pa.id_product)
LEFT JOIN stock_available s ON (p.id_product = s.id_product
and ((pa.id_product_attribute is null and s.id_product_attribute = 0)
or (s.id_product_attribute = pa.id_product_attribute)))
LEFT JOIN manufacturer m ON (p.id_manufacturer = m.id_manufacturer)
LEFT JOIN (SELECT
`v`.`id_product_attribute` AS `id_product_attribute`,
`v`.`id_lang` AS `id_lang`,
GROUP_CONCAT(CONCAT(`v`.`group_name`,' ',`v`.`attribute_name`) ORDER BY `v`.`group_position` ASC,`v`.`attribute_position` ASC SEPARATOR ',') AS `combinaison`
FROM (
SELECT `pac`.`id_product_attribute` AS `id_product_attribute`,
`pa`.`id_product` AS `id_product`,
`al`.`id_lang` AS `id_lang`,
`a`.`id_attribute` AS `id_attribute`,
`a`.`id_attribute_group` AS `id_attribute_group`,
`ag`.`position` AS `group_position`,
`a`.`position` AS `attribute_position`,
`agl`.`public_name` AS `group_name`,
`al`.`name` AS `attribute_name`
FROM `product_attribute_combination` `pac`
JOIN `attribute` `a` ON`a`.`id_attribute` = `pac`.`id_attribute`
JOIN `attribute_lang` `al` ON`al`.`id_attribute` = `pac`.`id_attribute`
JOIN `product_attribute` `pa` ON`pa`.`id_product_attribute` = `pac`.`id_product_attribute`
JOIN `attribute_group` `ag` ON`ag`.`id_attribute_group` = `a`.`id_attribute_group`
JOIN `attribute_group_lang` `agl` ON`agl`.`id_attribute_group` = `a`.`id_attribute_group` AND `agl`.`id_lang` = `al`.`id_lang`
) `v`
GROUP BY `v`.`id_product_attribute`,`v`.`id_lang`) cm ON cm.`id_product_attribute` = pa.`id_product_attribute` and cm.`id_lang` = pl.id_lang
WHERE pl.id_lang = 1
ORDER BY p.id_manufacturer, p.id_product