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.


Link to comment
Share on other sites

  • 1 year later...

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...