Jump to content

PrestaShop 1.6 get product discounted price using sql

Recommended Posts


Greetings of the day.

I want to get product discounted price using sql. I am able to get regular price.

For example below is the discounted price.

17,83 € tax incl.


19,81 € tax incl.


SELECT CONCAT('PS', p.id_product) AS 'sku',
    p.active AS 'Active (0/1)',
    pl.name AS 'post_title',
    GROUP_CONCAT(DISTINCT REPLACE(pc.name, ' / ', '>')  SEPARATOR '|') AS 'tax:product_cat',
    p.price AS 'Price tax excl.',
    ROUND(p.price * (COALESCE(ptx.rate, 0) / 100 + 1), 2) AS 'regular_price',
    COALESCE(ptx.rate, 0) AS 'Tax',
    p.wholesale_price AS 'Wholesale price',
    p.on_sale AS 'On sale (0/1)',
    p.reference AS 'Reference #',
    p.supplier_reference AS 'Supplier reference #',
    p.ean13 AS 'EAN13',
    p.upc AS 'UPC',
    p.ecotax AS 'Ecotax',
    p.weight AS 'weight',
    p.quantity AS 'stock',
    "yes" AS 'manage_stock',
    pl.description_short AS 'post_excerpt',
    pl.description AS 'post_content',
    pl.meta_title AS 'Meta-title',
    pl.meta_keywords AS 'Meta-keywords',
    pl.meta_description AS 'Meta-description',
    pl.link_rewrite AS 'URL rewritten',
    pl.available_now AS 'Text when in stock',
    pl.available_later AS 'Text when backorder allowed',
    p.available_for_order AS 'Available for order',
    p.date_add AS 'post_date',
    p.show_price AS 'Show price',
    p.online_only AS 'Available online only',
    p.`condition` AS 'Condition',
    concat( 'http://svartrecords.com/shoppe/img/p/',mid(im.id_image,1,1),'/', if (length(im.id_image)>1,concat(mid(im.id_image,2,1),'/'),''),if (length(im.id_image)>2,concat(mid(im.id_image,3,1),'/'),''),if (length(im.id_image)>3,concat(mid(im.id_image,4,1),'/'),''),if (length(im.id_image)>4,concat(mid(im.id_image,5,1),'/'),''), im.id_image, '.jpg' ) AS 'images'
FROM ps_product p LEFT JOIN
ps_product_lang pl ON p.id_product = pl.id_product AND pl.id_lang = 1 JOIN
ps_tax_rule ptxgrp ON ptxgrp.id_tax_rules_group = p.id_tax_rules_group JOIN
ps_tax ptx ON ptx.id_tax = ptxgrp.id_tax  JOIN
ps_image im ON p.id_product = im.id_product JOIN
ps_category_product pcp ON pcp.id_product = p.id_product JOIN
ps_category_lang pc ON pcp.id_category = pc.id_category AND pc.id_lang = 1
WHERE p.active = 1
GROUP BY p.id_product

SELECT DISTINCT product_id, product_reference AS kood,product_name AS nimetus,product_quantity AS nr, pl.link_rewrite,psa.quantity as kogukogus,group_concat(ps.product_supplier_reference) as supp_ref, p.location AS asukoht,
         CONCAT(c.link_rewrite,'/',p.id_product,'-',pl.link_rewrite,'.html') link
        FROM ps_order_detail o
        JOIN ps_product_lang pl on o.product_id = pl.id_product
        JOIN ps_product p on p.id_product = pl.id_product
        JOIN ps_stock_available psa on p.id_product = psa.id_product
        JOIN ps_category_lang c on c.id_category = p.id_category_default
        JOIN ps_product_supplier ps on p.id_product = ps.id_product
        LEFT JOIN ps_product_attribute pa on p.id_product=pa.id_product
                   AND psa.id_product_attribute = pa.id_product_attribute

        WHERE pl.id_lang=2 AND c.id_lang=2

SELECT p.id_product, p.active, pl.name, GROUP_CONCAT(DISTINCT(cl.name) SEPARATOR ",") as categories, p.price, p.id_tax_rules_group, p.wholesale_price, p.reference, p.supplier_reference, p.id_supplier, p.id_manufacturer, p.upc, p.ecotax, p.weight, p.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
FROM ps_product p
LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product)
LEFT JOIN ps_category_product cp ON (p.id_product = cp.id_product)
LEFT JOIN ps_category_lang cl ON (cp.id_category = cl.id_category)
LEFT JOIN ps_category c ON (cp.id_category = c.id_category)
LEFT JOIN ps_product_tag pt ON (p.id_product = pt.id_product)
WHERE pl.id_lang = 1
AND cl.id_lang = 1
AND p.id_shop_default = 1
AND c.id_shop_default = 1
GROUP BY p.id_product    


Any idea?   

Have a nice day.


Share this post

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...

Important Information

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