I am developing an ecommerce platform, and I came across a problem to calculate the value of the products by applying discount on the SQL query, are being applied in PHP, but when I order sort 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 greater value than he.
Note: Promotions may be associated with categories or products themselves and a product / category may have more than one associated promotion, promotions may accumulate according to an existing flag within the promotion table and discounts can be either 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 thus (in a simplified form without taking into consideration 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 table looks 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 promotions tables that links products to promotions and promotions categories that relate categories to promotions.
Has anyone ever done something like this or did you know of a solution to this?