Update multiple records at once using SailsJS

Question

So I'm making this app where I will have menu items for a restaurant right ?

The owner should have the ability to move around the menu items

This is my menuitem.js Model contains

name , price , position

That's it. Super simple.

So to reposition these elements I will use jquery UI So 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 on the database every time one of the menu items has been changed. So I thought Sails must have a function that can update all records at once

Like 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 opened an issue on SailsJS https://github.com/balderdashy/sails/issues/2404

Using Mongoose instead of Waterline, I know I have this ability, but I don't use Waterline, you should check the doc. Waterline is the "ORM" used by sails. — Vadorequest, Nov 16 '14 at 01:30
Btw, questions about sails are usually written at https://groups.google.com/forum/#!forum/sailsjs instead of github which is more for issues, bugs and so. I just think you could get a faster answer on GoogleGroups than github, just saying. — Vadorequest, Nov 16 '14 at 12:09
seems like you need to use native query for this. What's your underlying database ? — Mandeep Singh, Nov 24 '14 at 15:26
nah man. This could potentially open up to SQL injection and more issues in my code — Andre Frexio, Nov 28 '14 at 20:32
agreed, but that can be fixed by escaping user provided data in the query. Take a look here https://github.com/felixge/node-mysql#escaping-query-values — Mandeep Singh, Nov 29 '14 at 04:48

score 0 · Answer 1 · answered Nov 26 '14 at 10:31

You can achieve this using a native SQL query. To execute native SQL queries, you need to use Model.query() function of 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 native SQL query, you can use case statement to update the rows depending on the position value. For example, if I am moving item 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;

Ofcourse, 4 and 2 will be supplied dynamically and your query might look more complex since you will also need to handle whether the user is moving an object up or down. But this is how you can achieve this. If you need to execute even more complicated logic, you can create a stored procedure also. That will also be executed by using Model.query. Beware of SQL injection attacks while building your dynamic queries using the parameters supplied by the end user.

Update multiple records at once using SailsJS

1 Answers1