2
I’m developing a platform Ecommerce, and I came across a problem to calculate the value of products, applying the discount of promotions in consultation SQL, currently the promotion rules are being applied in PHP, but when ordered by value, it sorts without taking into account the value of the product with the promotion applied, and that is not the goal, I need the product that has the promotion in the list to be before the products of higher value than it.
Obs.: Promotions can be associated to categories or to the products themselves and a product/category can have more than one associated promotion, the promotions can accumulate according to an existing flag within the promotion table and the discounts can be fixed price or percentage according to the type field existing within the promotion, which determines whether the discount is percentage or value.
Currently my query returns the products is like this (in a simplified way without taking into account other related tables that do not interfere in my question):
SELECT p.nome, MIN(e.valor) AS valor, p.id_produto, c.categoria
FROM produtos p
INNER JOIN estoque e ON p.id_produto = e.id_produto
INNER JOIN categorias c ON p.id_categoria = c.id_categoria
GROUP BY p.id_produto
ORDER BY valor, p.id_produto
And my promotion chart is like this:
id_promocao INT
data_inicial DATETIME
data_final DATETIME
acumular TINYINT(1)
ativo TINYINT(1)
tipo TINYINT(1)
valor DECIMAL(10,2)
And there are the tables products_promocoes relating products to promotions and categories_promocoes relating categories to promotions.
Someone has done something similar or knows a solution to it?