Sequelize query to subtract value from previous row value

Question

I'm using PostgreSQL. I have a reading value in my table. From that, I have to find the consumption by subtracting previous value.

I found the SQL query. How to use that in sequelize? Is there any other option except raw queries?

SQL Query:

SELECT "readingValue",
       "readingValue" - COALESCE(LAG("readingValue") OVER
           (ORDER BY "readingTime")) AS consumption
FROM public."tableName" LIMIT 100;

Check this : https://stackoverflow.com/a/48779426/2349407 , this might help you with current one. — Vivek Doshi, Dec 11 '18 at 05:35
@VivekDoshi - I have checked that. That query is to update the record in the table. I don't want to update the table. — Jegan, Dec 11 '18 at 05:44

score 3 · Answer 1 · answered May 13 '20 at 16:06

3

You can do that with sequelize literal.

model.update({'count': sequelize.literal('count + 1')}, { where: { id: 1 }})

You can also do it with increment method.

Model.increment('seq', { by: 5, where: { id: 'model_id' });

answered May 13 '20 at 16:06

Siddharth Sunchu

866
10
13

Tim Biegeleisen · Answer 2 · 2018-12-11T06:23:00.323

0

I don't know if this would help, but you could also avoid using LAG and instead use a correlated subquery to find the lag value:

SELECT
    readingValue,
    readingValue - (SELECT t2.readingValue FROM public."tableName" t2
                    WHERE t2.readingValue < t1.readingValue
                    ORDER BY t2.readingValue DESC LIMIT 1) AS consumption
FROM public."tableName" t1;

Maybe this would be easier to phrase using your JavaScript ORM framework.

edited Dec 11 '18 at 06:23

answered Dec 11 '18 at 05:39

Tim Biegeleisen

502,043
27
286
360

But subqueries tales more time than the MySQL LAG function. – Jegan Dec 11 '18 at 06:06
Then use a raw query with `LAG`. What are you asking here? – Tim Biegeleisen Dec 11 '18 at 06:07
are there any other options except raw query? Because I have a complex where condition. that's why. – Jegan Dec 11 '18 at 06:15

score 0 · Answer 3 · answered Dec 11 '18 at 07:52

We can use sequelize.literal for a field in attributes.

model.findAll({
        where: whereConditionObj,
        attributes: ['id','readingValue',
            [
                db.sequelize.literal('"readingValue" - COALESCE(LAG("readingValue") OVER (ORDER BY "readingTime"))'), 'Consumption'
            ]
        ],
        order: [['readingTime', 'ASC']],
        group:['id', 'readingValue']
    })

Sequelize query to subtract value from previous row value

3 Answers3