Update multiple records at once with SailsJS

Question

So, I'm doing this app, where will I have menu items for the restaurant?

The owner should be able to navigate through the menu items.

This is my model menuitem.js contains

name, price, position

What is it. Super simple.

So, to move these elements, I will use jQuery UI Thus, the final product will be something very similar to this> http://jqueryui.com/sortable/

So, my goal is to save the position of each of these menu items in the database every time one of the menu items has been changed. So I thought Sails should have a function that can update all records at once.

As below

Menuitem.update([{id: 1},{position: 3}], [{id: 2},{position: 3}] ).exec(function(err, updatedRecords) { if (err) { return res.send({message: 'Could not update the records', err: err}, 500); } if (updatedRecords) { return res.send({ records: updatedRecords }, 200); } else { return res.notFound('Records not found'); } }); 

Any help would be greatly appreciated.

Also a problem opened with SailsJS https://github.com/balderdashy/sails/issues/2404

Answer 1

You can achieve this using your own SQL query. To execute your own SQL queries, you need to use the Model.query () function for the waterline. In your case, it will be something like this.

 var myQuery = "update menuitem set .... complete SQL query goes here "; Menuitem.query(myQuery, function (err, result){ if(err){ // error handling here } else{ // do something with result here } }); 

Now, for your own SQL query, you can use the case statement to update the rows depending on the position value. For example, if I move an element from position 4 to position 2, then my query will be like this.

 update menuitem set position = case when position = 4 then 2 when position >=2 then position + 1 else position end; 

Of course, 4 and 2 will be delivered dynamically, and your request may look more complex, as you will also need to process whether the object moves up or down. But so you can achieve this. If you need to perform even more complex logic, you can also create a stored procedure. This will also be done using Model.query. Beware of SQL injection attacks when building dynamic queries using parameters provided by the end user.