How to nest SQL functions in sequelize.js?

Question

I'm using Sequelize.JS to connect to a MySQL database. I have the following table structure:

products
---------------------
id INT
name VARCHAR
price DECIMAL
price_special DECIMAL

I'd like to get the lowest price (or special_price, when available) and the highest price (or special_price when available). With raw SQL, I can do the following:

SELECT 
    MIN(IF(`price_special` > 0, `price_special`, `price`)) AS `min`, 
    MAX(IF(`price_special` > 0, `price_special`, `price`)) as `max` 
FROM 
    `products`
;

I also know I can select a column wrapped by an SQL function in Sequelize, this way:

product.findOne({
    attributes: [
        [ Sequelize.fn('MIN', Sequelize.col('price')), 'min' ],
        [ Sequelize.fn('MAX', Sequelize.col('price')), 'max' ],
    ]
});

However, I don't know how to to nest MIN and IF functions, the way I do with raw SQL.

Gustavo Straube · Accepted Answer · 2016-01-22 13:57:07Z

2

After posted my question I found an issue in GitHub with a similar problem.

The solution I got is the following:

product.findOne({
    attributes: [
        [ Sequelize.fn('MIN', Sequelize.fn('IF',
                Sequelize.literal('`price_special` > 0'),
                Sequelize.col('price_special'),
                Sequelize.col('price'))), 'min' ],
        [ Sequelize.fn('MAX', Sequelize.fn('IF', 
                Sequelize.literal('`price_special` > 0'), 
                Sequelize.col('price_special'), 
                Sequelize.col('price'))), 'max' ],
    ]
});

I personally don't like the Sequelize.literal call, but it's working and I don't know how to improve it.

answered Jan 22, 2016 at 13:57

Gustavo Straube

3,9016 gold badges45 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to nest SQL functions in sequelize.js?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related