Jump to content
PrestashopUser03

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

Recommended Posts

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 :)

Share this post


Link to post
Share on other sites

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/)

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...

Important Information

Cookies ensure the smooth running of our services. Using these, you accept the use of cookies. Learn More