Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Bookshelf.js update query not returning expected data #2100

Open
jaisam opened this issue Feb 5, 2021 · 0 comments
Open

Bookshelf.js update query not returning expected data #2100

jaisam opened this issue Feb 5, 2021 · 0 comments

Comments

@jaisam
Copy link

jaisam commented Feb 5, 2021

Bookshelf.js update command not returning expected data after updating rows in database. This is observed if instead of using id-attrtibute, a where clause is used to add conditions for the update query.

Pre-requisites :
I have below dummy table structure (If anyone wants to replicate can use below query) :

CREATE TABLE `dummy` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `first_name` varchar(20) NOT NULL,
 `last_name` varchar(20) NOT NULL,
 `status` tinyint(1) NOT NULL COMMENT '1:Active, 9:Deleted',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

Below are the rows in the table :

INSERT INTO `dummy` (`id`, `first_name`, `last_name`, `status`) VALUES (NULL, 'Jai', 'Samtani', '1');
INSERT INTO `dummy` (`id`, `first_name`, `last_name`, `status`) VALUES (NULL, 'Jai', 'Sam', '1');
INSERT INTO `dummy` (`id`, `first_name`, `last_name`, `status`) VALUES (NULL, 'Jai', 'S', '1');
INSERT INTO `dummy` (`id`, `first_name`, `last_name`, `status`) VALUES (NULL, 'Bharti', 'S', '1');

Model Definition using Bookshelf.js :

const Dummy = dbconnection.Model.extend({
	tableName: 'dummy',
});

export default Dummy;

Issue #1: I want to set status to 9 for [1,2,3] id's and i'm using below query for the same:

let deletedDummyData = await Dummy.forge().where('id', 'IN', [1,2,3]).save({status: this.configVal.STATUS.delete}, {method: 'update', patch: true, debug: true});
console.log('deletedDummyData ===============>', deletedDummyData);

Ideally Bookshelf.js should return all 3 rows but it is returning only 1 row. Although bookshelf.js has updated 3 rows in db.

Here is what bookshelf.js is returning :

{
  method: 'update',
  options: {},
  timeout: false,
  cancelOnTimeout: false,
  bindings: [ 9, 1, 2, 3 ],
  __knexQueryUid: '3XAQVibd2zeX1ZPgEBmb_',
  sql: 'update `dummy` set `status` = ? where `id` in (?, ?, ?)'
}
deletedDummyData ===============> ModelBase {
  attributes: [Object: null prototype] {
    status: 9,
    id: 1,
    first_name: 'Jai',
    last_name: 'Samtani'
  },
  _previousAttributes: {},
  changed: [Object: null prototype] {},
  relations: {},
  cid: 'c1',
  _knex: null,
  id: 1
}

Has anyone faced such issue or am I using wrong query?


Issue #2 : : I want to update last_name to 'Sam' of a row whose first_name is 'Bharti' and below is the query I'm using :

let updatedDummyData = await Dummy.forge().where({first_name : "Bharti"}).save({last_name: "Sam"}, {method: 'update', patch: true, debug: true});
console.log('updatedDummyData ==>', updatedDummyData);

Ideally I should get the row whose first_name is "Bharti" but I'm getting some vague row. Although behind the scenes, bookshelf has updated the right row in db.
Here is what bookshelf.js is returning :

{
  method: 'update',
  options: {},
  timeout: false,
  cancelOnTimeout: false,
  bindings: [ 'Sam', 'Bharti' ],
  __knexQueryUid: 'Mrf9z4J7wbQ753BXPkpG9',
  sql: 'update `dummy` set `last_name` = ? where `first_name` = ?'
}
updatedDummyData ==> ModelBase {
  attributes: [Object: null prototype] {
    last_name: 'Sam',
    id: 2,
    first_name: 'Jai',
    status: 9
  },
  _previousAttributes: {},
  changed: [Object: null prototype] {},
  relations: {},
  cid: 'c2',
  _knex: null,
  id: 2
}

Has anyone faced such issue or am I using wrong query?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant