Aide sur une jointure de table, récupérer les marques des articles

[PrestashopUser03]

Bonjour tout le monde,

J'ai une requête SQL qui fonctionne très bien, mais je n'arrive pas à récupérer le nom de la marque, vous pouvez m'aider?

 

SELECT p.id_product, pa.ean13,pa.upc,p.active, p.isBike, p.isDirect, p.weight,p.height, p.width, p.depth, pa.reference, pl.description_short, pa.upc, p.price, pai.id_image, pl.name, GROUP_CONCAT( DISTINCT (
pal.name
)
SEPARATOR ", " ) AS combination, pq.quantity
FROM ps_product p
LEFT JOIN ps_product_attribute pa ON ( p.id_product = pa.id_product )
LEFT JOIN ps_stock_available pq ON ( p.id_product = pq.id_product
AND pa.id_product_attribute = pq.id_product_attribute )
LEFT JOIN ps_product_lang pl ON ( p.id_product = pl.id_product )
LEFT JOIN ps_product_attribute_combination pac ON ( pa.id_product_attribute = pac.id_product_attribute )
LEFT JOIN ps_attribute_lang pal ON ( pac.id_attribute = pal.id_attribute )
LEFT JOIN ps_product_attribute_image pai ON ( pa.id_product_attribute = pai.id_product_attribute )
WHERE pl.id_lang =1
AND pal.id_lang =1
GROUP BY pac.id_product_attribute';

 

Merci à tous

[cyssoo]

Normal, il manque totalement la jointure SQL sur les marques façon "LEFT JOIN ps_manufacturer m ON p.id_manufacturer = m.id_manufacturer" (mais je confonds toujours les marques et les fabricants sous Prestashop, je les appelle "tic et tac")

Attention aux jointures. Tout n'est pas forcément LEFT JOIN :

"Petit" spoiler de Ever Export :

        if (!(int)Configuration::get('EVEREXPORT_RENDER_ALL')) {
            $and = 'AND cp.id_category = '.(int)Configuration::get('EVEREXPORT_CATEGORY');
        } else {
            $and = '';
        }
        //Need to export attributes
        $resproducts = Db::getInstance()->query(
            'SELECT
            pl.description_short,
            p.id_product,
            pl.name product_name,
            cl.name category_name,
            m.name AS manufacturer,
            p.price,
            p.id_tax_rules_group,
            p.reference,
            sa.quantity,
            pl.description,
            pl.meta_title,
            pl.meta_description,
            pl.link_rewrite,
            p.date_add,
            p.weight,
            p.active AS pactive,
            p.supplier_reference
        FROM '._DB_PREFIX_.'product AS p
            INNER JOIN '._DB_PREFIX_.'product_lang AS pl ON (p.id_product = pl.id_product)
            LEFT JOIN '._DB_PREFIX_.'stock_available AS sa ON (sa.id_product = p.id_product)
            LEFT JOIN '._DB_PREFIX_.'category_product AS cp ON (cp.id_product = p.id_product)
            LEFT JOIN '._DB_PREFIX_.'category_lang AS cl ON (cl.id_category = cp.id_category)
            LEFT JOIN '._DB_PREFIX_.'manufacturer AS m ON (m.id_manufacturer = p.id_manufacturer)
        WHERE pl.id_lang = '.(int)$this->context->language->id.'
            AND sa.quantity > 0
            AND p.active = 1
            AND p.id_shop_default = '.(int)$id_shop.'
            '.$and.'
        GROUP BY p.id_product'
        );

        $rescombinations = Db::getInstance()->query(
            'SELECT m.name AS manufacturer, p.id_product, pl.name, GROUP_CONCAT(DISTINCT(al.name) SEPARATOR ", ") AS combinations, 
            GROUP_CONCAT(DISTINCT(cl.name) SEPARATOR ",") AS categories,
            (p.price + pa.price) AS pprice,
            p.wholesale_price AS wholesale_price,
            p.id_tax_rules_group,
            pa.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,
            p.active AS pactive
            FROM '._DB_PREFIX_.'product p
            LEFT JOIN '._DB_PREFIX_.'product_lang pl ON (p.id_product = pl.id_product)
            LEFT JOIN '._DB_PREFIX_.'manufacturer m ON (p.id_manufacturer = m.id_manufacturer)
            LEFT JOIN '._DB_PREFIX_.'category_product cp ON (p.id_product = cp.id_product)
            LEFT JOIN '._DB_PREFIX_.'category_lang cl ON (cp.id_category = cl.id_category)
            LEFT JOIN '._DB_PREFIX_.'category c ON (cp.id_category = c.id_category)
            LEFT JOIN '._DB_PREFIX_.'stock_available s ON (p.id_product = s.id_product)
            LEFT JOIN '._DB_PREFIX_.'product_tag pt ON (p.id_product = pt.id_product)
            LEFT JOIN '._DB_PREFIX_.'product_attribute pa ON (p.id_product = pa.id_product)
            LEFT JOIN '._DB_PREFIX_.'product_attribute_combination pac ON (pac.id_product_attribute = pa.id_product_attribute)
            LEFT JOIN '._DB_PREFIX_.'attribute_lang al ON (al.id_attribute = pac.id_attribute)
            WHERE pl.id_lang = '.(int)$this->context->language->id.'
            AND cl.id_lang = '.(int)$this->context->language->id.'
            AND p.id_shop_default = '.$id_shop.'
            '.$and.'
            GROUP BY pac.id_product_attribute'
        );
        if ((int)Configuration::get('EVEREXPORT_COMBINATIONS')) {
            $export = $rescombinations;
        } else {
            $export = $resproducts;
        }
        return $export;

(source : https://www.team-ever.com/produit/everexport-export-des-donnees-au-format-excel-prestashop/)