devtex

Try with the following request. It will select the fist active shop id and lang id. SELECT p.id_product, pl.name, p.price, p.reference, pl.description_short, pl.description FROM ps_product p LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product) WHERE p.active = 1 AND pl.id_lang = (SELECT id_lang FROM ps_lang WHERE active = '1' ORDER BY id_lang ASC LIMIT 1) AND p.id_shop_default = (SELECT id_shop FROM ps_shop WHERE active = '1' ORDER BY id_shop ASC LIMIT 1)

I don't understand what you mean by "the descriptions are in text format, no html". If you want the images url, it's a little bit more tricky Please find the code there: SELECT p.id_product, pa.reference, pl.name, @id_image := ifnull(pai.id_image, pi.id_image) as id_image, concat('http://', ifnull(shop_domain.value, 'domain'), '/img/p/', if(CHAR_LENGTH(@id_image) >= 5, concat(SUBSTRING(@id_image from - 5 FOR 1), '/'), ''), if(CHAR_LENGTH(@id_image) >= 4, concat(SUBSTRING(@id_image from - 4 FOR 1), '/'), ''), if(CHAR_LENGTH(@id_image) >= 3, concat(SUBSTRING(@id_image from - 3 FOR 1), '/'), ''), if(CHAR_LENGTH(@id_image) >= 2, concat(SUBSTRING(@id_image from - 2 FOR 1), '/'), ''), if(CHAR_LENGTH(@id_image) >= 1, concat(SUBSTRING(@id_image from - 1 FOR 1), '/'), ''), @id_image, '.jpg') as image_url, GROUP_CONCAT(DISTINCT (pal.name) SEPARATOR ', ') as combination, ROUND(p.price, 2) as price, p.active, 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) LEFT JOIN ps_image pi ON p.id_product = pi.id_product LEFT JOIN ps_configuration shop_domain ON shop_domain.name = 'PS_SHOP_DOMAIN' WHERE pl.id_lang = ( SELECT id_lang FROM ps_lang ORDER BY id_lang ASC LIMIT 1 ) AND pal.id_lang = pl.id_lang GROUP BY pa.reference ORDER BY p.id_product , pac.id_attribute;

If you want to add the description, you'll need to add a line with pl.description in the SELECT statement.

If you use the SQL Manager, you can start with something like this: SELECT p.id_product, pa.reference, pl.name, GROUP_CONCAT(DISTINCT(pal.name) SEPARATOR ", ") as combination, p.price, 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) WHERE pl.id_lang = 1 AND pal.id_lang = 1 GROUP BY pa.reference ORDER BY p.id_product, pac.id_attribute This fitted my needs.

+1

Has anyone a solution? I had to set the status to "Cancel". I found no other possibilities.

I have the same requirement under version 1.6.0.9 Is there a solution?

Hello, I am creating a module for the latest version of PS. It is a payment module which connects to an external api to create order with a certain status. My shop doesn't use the advanced stock managment, but a new order decrease the products stock even if the status is in error. Can you please tell me how to define a status that won't lower the stock in any cases? Thank you for your help, Manu