SELECT p.*, pl.*, CONCAT("http://вашсайт.ru/", cl.link_rewrite, "/", p.id_product, "-", pl.link_rewrite, ".html") link, m.name manufacturer_name, pa.id_product_attribute, agl.public_name public_group_name, al.name attribute_name

FROM ps_product p
LEFT JOIN ps_product_attribute pa ON (pa.id_product = p.id_product)
LEFT JOIN ps_product_attribute_combination pac ON (pac.id_product_attribute = pa.id_product_attribute)
LEFT JOIN ps_attribute a ON (a.id_attribute = pac.id_attribute)
LEFT JOIN ps_attribute_group ag ON (ag.id_attribute_group = a.id_attribute_group)
LEFT JOIN ps_attribute_lang al ON (al.id_attribute  = a.id_attribute AND al.id_lang = 1)
LEFT JOIN ps_attribute_group_lang agl ON (agl.id_attribute_group = ag.id_attribute_group AND agl.id_lang = 1)

LEFT JOIN ps_product_lang pl ON (pl.id_product = p.id_product)
LEFT JOIN ps_category_lang cl ON (cl.id_category = p.id_category_default AND cl.id_lang = 1)
LEFT JOIN ps_manufacturer m ON (m.id_manufacturer = p.id_manufacturer)

WHERE
    pl.id_lang = 1

$id_lang = 1;
$products = Product::getProducts($id_lang, 0, 99999, 'id_product', 'ASC');
foreach($products as &$product)
{
    $product['attributes'] = [];
    if($product['cache_default_attribute'])
    {
        $p = new Product($product['id_product']);
        $product['attributes'] = $p->getAttributeCombinaisons($id_lang);
    }
}