CODEWITHSUNDEEP
node.jspostgresqlsequelize.js
Benfactor
Benfactor

Reputation: 599

How to use 'select if' function with sequelize in nodejs

I am developing a rest api in nodejs. I am using the sequelize library with a postgresql database.

With sequelize in nodejs I want to get a temporary column to manage some values. I have two column vip_start and vip_end. These columns are DataTypes.DATEONLY format.

When I select all the columns, I want to compare two dates, and finally create a new column with the answer. If true then 1, else 0. And then I want to order the selected rows by VIP_ANSWER (1 or 0).

In mysql it solves that like this:

( SELECT IF (CURDATE( ) >= DATE( vip_start ) AND CURDATE( ) <= DATE( vip_end ), 1, 0 ) ) as vip

How can I make it in sequelize? :

const datetime = new Date();
return Phones
    .findAll({
        attributes: ['id', 'description',  'vip_start', 'vip_end','user_id' ],//I want to there new column like vip_answer [1,0] and order by this values
         where: {
             enabled: 1,
             vip_start: { $lte: datetime }, // in there i am stopped (((
         },
       })
    .then(phone => res.status(200).send(phone));

Any help will be appreciated.

Upvotes: 3

Views: 5617

Answers (1)

Vivek Doshi
Vivek Doshi

Reputation: 58593

Just add 

[ sequelize.literal('( SELECT IF (CURDATE( ) >= DATE( vip_start ) AND CURDATE( ) <= DATE( vip_end ), 1, 0 ) )'),'vip']

To your attribute list :

attributes: ['id', 'description',  'vip_start', 'vip_end','user_id' , [ sequelize.literal('( SELECT IF (CURDATE( ) >= DATE( vip_start ) AND CURDATE( ) <= DATE( vip_end ), 1, 0 ) )'),'vip'] ]

Upvotes: 3

Related Questions