CODEWITHSUNDEEP
sqlpostgresqlknex.js
Jakub
Jakub

Reputation: 2709

How to express a 'smaller than or equals' relation in Knex.js

In my back-end I'm using KnexJS with PostgreSQL and I have to build a Knex function using its SQL builder without the raw SQL.

It is the first time for me to use KnexJS and I got few issues.

What I have to build is as shown in the SQL example

 UPDATE
     feed
  SET
     status = 'SOME_STATUS'
  WHERE
     created_at <= 'SOME_TIMESTAMP'
     AND conversation_id = 'ID';

This SQL is updating the table feed all columns with status and where two conditions are met.

In Knex what I tried as example code of my idea

answerPendingMessages(feeds) {
    return this.tx(tableName).where({
      conversationId,
      createdAt <= timestamp  // This not idea how to do in Knex ???
    }).update({
      status: 'ANSWERED'
    })
  }

In this above function my concern is how to actually convert the where part as one of the consitions is createdAt <= 'TIMESTAMP'

I understood that I can use where with an object but cannot understand how to include the <=

Also I should update the updatedAt column with the new timestamp but also that blocked me at the moment.

Te result in the end should that all columns which met the conditions are updated status to some some status and also a new updatedAt timestamp.

I'm not sure if there is a specific way with Knex to do so

Upvotes: 1

Views: 216

Answers (1)

Jakub
Jakub

Reputation: 2709

This answer is to provide my goal for my previous question. The script I'm showing is not doing anything and I don't know know to make it to work.

answerPendingMessages(feeds) {
    if (isEmpty(feeds)) return Promise.resolve([]);
    const { conversationId, createdAt } = feeds;
    return this.tx(tableName)
      .where(
        columns.conversationId === conversationId,
        columns.createdAt <= createdAt
      )
      .update({
        [columns.status]: 'ANSWERED',
        [columns.updatedAt]: new Date(),
      });
  }

What should happen here is that to update a table where I have two conditions but one of then is 'smaller than or equals' relation.

As the output of this should be that all rows where the 2 conditions are met are update status column.

Right now the script is not failing either success piratically nothing is happening.

Upvotes: 1

Related Questions