SQL manager @id_lang := 1 AS id_lang,

[Alexandre Carette]

Bonjour,
Je souhaite ajouter des variables à une requete SQL (qui marche via phpmyadmin)

SET @id_shop = 1;
SET @id_lang = 1;
SET @domain = 'http://test.com';

J'ai overridé la classe RequestSql.php pour accepter le mot clef SET

class RequestSql extends RequestSqlCore
{
	/** @var array : List of params to tested */
    public $tested = array(
        'required' => array('SELECT', 'FROM'),
        'option' => array('WHERE', 'ORDER', 'LIMIT', 'HAVING', 'GROUP', 'UNION'),
        'operator' => array(
            'AND', '&&', 'BETWEEN', 'AND', 'BINARY', '&', '~', '|', '^', 'CASE', 'WHEN', 'END', 'DIV', '/', '<=>', '=', '>=',
            '>', 'IS', 'NOT', 'NULL', '<<', '<=', '<', 'LIKE', '-', '%', '!=', '<>', 'REGEXP', '!', '||', 'OR', '+', '>>', 'RLIKE', 'SOUNDS', '*',
            '-', 'XOR', 'IN'
        ),
        'function' => array(
            'AVG', 'SUM', 'COUNT', 'MIN', 'MAX', 'STDDEV', 'STDDEV_SAMP', 'STDDEV_POP', 'VARIANCE', 'VAR_SAMP', 'VAR_POP',
            'GROUP_CONCAT', 'BIT_AND', 'BIT_OR', 'BIT_XOR'
        ),
        'unauthorized' => array(
            'DELETE', 'ALTER', 'INSERT', 'REPLACE', 'CREATE', 'TRUNCATE', 'OPTIMIZE', 'GRANT', 'REVOKE', 'SHOW', 'HANDLER',
            'LOAD', 'ROLLBACK', 'SAVEPOINT', 'UNLOCK', 'INSTALL', 'UNINSTALL', 'ANALZYE', 'BACKUP', 'CHECK', 'CHECKSUM', 'REPAIR', 'RESTORE', 'CACHE',
            'DESCRIBE', 'EXPLAIN', 'USE', 'HELP', 'DUPLICATE', 'VALUES',  'INTO', 'RENAME', 'CALL', 'PROCEDURE',  'FUNCTION', 'DATABASE', 'SERVER',
            'LOGFILE', 'DEFINER', 'RETURNS', 'EVENT', 'TABLESPACE', 'VIEW', 'TRIGGER', 'DATA', 'DO', 'PASSWORD', 'USER', 'PLUGIN', 'FLUSH', 'KILL',
            'RESET', 'START', 'STOP', 'PURGE', 'EXECUTE', 'PREPARE', 'DEALLOCATE', 'LOCK', 'USING', 'DROP', 'FOR', 'UPDATE', 'BEGIN', 'BY', 'ALL', 'SHARE',
            'MODE', 'TO','KEY', 'DISTINCTROW', 'DISTINCT',  'HIGH_PRIORITY', 'LOW_PRIORITY', 'DELAYED', 'IGNORE', 'FORCE', 'STRAIGHT_JOIN',
            'SQL_SMALL_RESULT', 'SQL_BIG_RESULT', 'QUICK', 'SQL_BUFFER_RESULT', 'SQL_CACHE', 'SQL_NO_CACHE', 'SQL_CALC_FOUND_ROWS', 'WITH'
        )
    );

}

 
ma requete est:

SET @id_shop = 1;
SET @id_lang = 1;
SET @domain = 'http://test.com';

SELECT 
p.id_product AS ID,
pl.name AS Nom,
pl.link_rewrite AS Link_rewrite, 
pl.description_short AS Desc_short, 
pl.description AS Desc_long, 
pl.meta_title AS Meta_title, 
pl.meta_keywords AS Meta_keyword, 
pl.meta_description AS Meta_desc,
p.reference AS Ref,
cl.id_category AS ID_Category,
cl.name AS Category_defaut,
GROUP_CONCAT(DISTINCT(cl.name) SEPARATOR ",") AS Categories_name,
GROUP_CONCAT(DISTINCT(c.id_category) SEPARATOR ",") AS Categories_id,
GROUP_CONCAT(DISTINCT(case
    when length(im.id_image)=6 then
     concat(@domain,'/img/p/',insert(insert(insert(insert(insert(im.`id_image`,2,0,'/'),4,0,'/'),6,0,'/'),8,0,'/'),10,0,'/'),'/',im.id_image,'.jpg')
    when length(im.`id_image`)=5 then
     concat(@domain,'/img/p/',insert(insert(insert(insert(im.`id_image`,2,0,'/'),4,0,'/'),6,0,'/'),8,0,'/'),'/',im.`id_image`,'.jpg')
    when length(im.`id_image`)=4 then
     concat(@domain,'/img/p/',insert(insert(insert(im.`id_image`,2,0,'/'),4,0,'/'),6,0,'/'),'/',im.`id_image`,'.jpg')
    when length(im.`id_image`)=3 then
     concat(@domain,'/img/p/',insert(insert(im.`id_image`,2,0,'/'),4,0,'/'),'/',im.`id_image`,'.jpg')
    when length(im.`id_image`)=2 then
     concat(@domain,'/img/p/',insert(im.`id_image`,2,0,'/'),'/',im.`id_image`,'.jpg')
    when length(im.`id_image`)=1 then
     concat(@domain,'/img/p/',insert(im.`id_image`,2,0,'/'),im.`id_image`,'.jpg')  
    else ''
    end) SEPARATOR ",") AS `Images`,
ml.`meta_title` AS `Marque`,
ml.`id_manufacturer` AS `Marque_ID`,
p.`price` AS `Prix`,
pshop.`id_tax_rules_group` AS `ID_tax_group`,
pshop.`on_sale` AS `On_sale`,
psp.`reduction` AS `reduction`,
psp.`reduction_tax` AS `tax_reduction`,
psp.`reduction_type` AS `type_reduction`,
psp.`from` AS `date_dbt_reduc`,
psp.`to` AS `date_fin_reduc`,
sa.`active` AS `Active`,
p.`ean13` AS `EAN13`,
p.`upc` AS 'UPC',
sav.`quantity` AS `Quantity`,
p.`visibility` AS `Visibility`, 
p.`indexed` AS `Indexed`, 
MAX(image_shop.id_image) id_image,
concat(pl.`link_rewrite`,'-',p.`id_product`,'.html')  AS `URL_google`,
concat(@domain,pl.`link_rewrite`,'-',p.`id_product`,'.html') AS `Produit_URL`,
case
    when length(im.`id_image`)=6 then
     concat(@domain,'/img/p/',insert(insert(insert(insert(insert(im.`id_image`,2,0,'/'),4,0,'/'),6,0,'/'),8,0,'/'),10,0,'/'),'/',im.`id_image`,'.jpg')
    when length(im.`id_image`)=5 then
     concat(@domain,'/img/p/',insert(insert(insert(insert(im.`id_image`,2,0,'/'),4,0,'/'),6,0,'/'),8,0,'/'),'/',im.`id_image`,'.jpg')
    when length(im.`id_image`)=4 then
     concat(@domain,'/img/p/',insert(insert(insert(im.`id_image`,2,0,'/'),4,0,'/'),6,0,'/'),'/',im.`id_image`,'.jpg')
    when length(im.`id_image`)=3 then
     concat(@domain,'/img/p/',insert(insert(im.`id_image`,2,0,'/'),4,0,'/'),'/',im.`id_image`,'.jpg')
    when length(im.`id_image`)=2 then
     concat(@domain,'/img/p/',insert(im.`id_image`,2,0,'/'),'/',im.`id_image`,'.jpg')
    when length(im.`id_image`)=1 then
     concat(@domain,'/img/p/',insert(im.`id_image`,2,0,'/'),im.`id_image`,'.jpg')  
    else ''
    end AS URL_IMG_1

FROM pss_product p 

LEFT JOIN pss_product_shop ps ON (p.id_product = ps.id_product)
LEFT JOIN pss_product_lang pl ON (p.id_product = pl.id_product AND pl.id_shop = @id_shop AND pl.id_lang = @id_lang)
LEFT JOIN pss_category_product cp ON (p.id_product = cp.id_product)
LEFT JOIN pss_category_lang cl ON (cp.id_category = cl.id_category ) 
LEFT JOIN pss_category c ON (cp.id_category = c.id_category)
LEFT JOIN pss_product_lang b ON (b.id_product = p.id_product AND b.id_lang = @id_lang AND b.id_shop = @id_shop) 
LEFT JOIN pss_image i ON (i.id_product = p.id_product) 
LEFT JOIN pss_manufacturer_lang ml ON (p.id_manufacturer = ml.id_manufacturer AND ml.id_lang = @id_lang )
LEFT JOIN pss_stock_available sav ON (sav.id_product = p.id_product AND sav.id_product_attribute = 0 AND sav.id_shop_group = 0  AND sav.id_shop = @id_shop )  
LEFT JOIN pss_product_shop sa ON (p.id_product = sa.id_product AND sa.id_shop = @id_shop) 
LEFT JOIN pss_shop shop ON (shop.id_shop = @id_shop)
LEFT JOIN pss_specific_price psp ON (psp.id_product = p.id_product) 
LEFT JOIN pss_product_shop pshop ON (pshop.id_product = p.id_product) 
LEFT JOIN pss_image_shop image_shop ON (image_shop.id_image = i.id_image AND image_shop.cover = 1 AND image_shop.id_shop = @id_shop) 
LEFT JOIN pss_product_download pd ON (pd.id_product = p.id_product) 
LEFT JOIN pss_image im on im.id_product= p.id_product

WHERE pl.id_lang = @id_lang AND ps.id_shop = @id_shop
GROUP BY p.id_product
ORDER BY p.id_product ASC 

J'obtiens une erreur: "Lorsque plusieurs tables sont utilisées, chaque attribut doit être référencé à l'une de ces tables."

ce serait génial de pouvoir utiliser cette requete via le bo de Prestashop, pouvez vous m'aider ?

 

cdt

Edited April 5, 2017 by Alexandre Carette (see edit history)