Utilizing Sequelize.JS for connecting to a MySQL database, the table structure is as follows:
products
---------------------
id INT
name VARCHAR
price DECIMAL
price_special DECIMAL
The goal is to retrieve the lowest price (or special_price if available) and the highest price (or special_price when present). This can be done in raw SQL with the following query:
SELECT
MIN(IF(`price_special` > 0, `price_special`, `price`)) AS `min`,
MAX(IF(`price_special` > 0, `price_special`, `price`)) as `max`
FROM
`products`
;
An alternative method involves selecting a column wrapped by an SQL function using Sequelize:
product.findOne({
attributes: [
[ Sequelize.fn('MIN', Sequelize.col('price')), 'min' ],
[ Sequelize.fn('MAX', Sequelize.col('price')), 'max' ],
]
});
However, the challenge lies in nesting the MIN and IF functions in Sequelize as it is done in raw SQL.