insert or update(upsert) in loopback application

Question

I developed an API using Loopback framework, in that i have to insert or update to a table.

My table looks like below:

userid  bbid saved  id(PK)
  1      5    1000   1

So when next time if(bbid = 5) it should update the above row, if bbid =5 is not there it should insert into the above table.

I tried the following:

app.models.modelname.upsert({
  bbid: bb.id,
  saved: Saved,
  userid: 1
}, function(err, res) {}); 

EDIT for COMPOSITE ID:

app.models.modelname.upsert({
  bbid: vvvv.bbid,
  userid: 1,
  saved: amountSaved
}, function(err, res) {});   

Also gone through Loopback doc it says

    upsert - checks if the instance (record) exists, based on the designated 
ID property, which must have a unique value; if the instance already exists, 
the method updates that instance.  Otherwise, it inserts a new instance.

But in my case it should check for bbid not id

But its inserting everytime. Please share your ideas. Thanks in advance

EDIT FOR UPSERT:

My process is as follows:

Fetch from table1 and loop that response and calculate the saved and upsert it into another table(table2 (this is where upsert should happen)). Also the fetching from table1 will happen frequently so suppose consider if is happens 2nd time it should update the already present bbid..

Mihrab

There is no way to use upsert with another key. It should be model key defined in mode.json. So you should fetch it and update/insert. There is extended key in loopback, but I think it doesn't work for upsert – Annatto

For last try yes. Please check this out docs.strongloop.com/display/public/LB/…. Define composite id for your model and upsert might work with that – Annatto

did you try composite id? – Annatto

For example : "id": { "type": "string", "id": true },"bbid": { "type": "string", "id": true } – Annatto

Did you put id in your data sent to upsert? – Annatto

@Ebrahim Pasbani I have edited the post what i tried?? Whether i have to give id or bbid in upsert statement.. Can you please provide the syntax for upsert.. Also id is auto-generated how can i give that in my upsert?? – Mihrab

If you don't have id how upsert should know insert or update?? So you should have id to do that. To clarify, where you call upsert? – Annatto

@Ebrahim Pasbani I edited the post. If needed i am ready to give more info.. – Mihrab

app.models.modelname.upsert({bbid:bb.id, saved: Saved, userid:1} ,function(err, res){ }); here you should provide id. If not, so upsert doesn't find id and calls insert. Maybe you should think about what is(are) id's in that model. What key(s) do you want for that model? – Annatto

@Ebrahim Pasbani As i already said id is auto-generated i cant give manually right?? Otherwise can i have only bbid in that table which is primary key.. – Mihrab

Yes, you can. In model.json you need to ignore id and set bbid as id of the model. "properties": { "id": false, "bbid": { "type": "number", "id": true } – Annatto

@Ebrahim Pasbani conradj solution is working.. Thanks for your valuable time.. – Mihrab

working but not fit to your question. You wanted upsert not finding – Annatto

@Ebrahim Pasbani yes its workaround,but no other way.. – Mihrab

Answer 1

You can use the findOrCreate method as follows:

app.models.modelname({where: {bbid:bb.id} }, {bbid:bb.id, saved: Saved, userid:1}, function(err, instance) {
    if (err){
        cb(null, err);
    }
        cb(null, instance);  
    });

Yevette

@Yevette Will this update the instance if found? Or does it just find the instance? – Psychopathist

@DevStar use upsertWithWhere if you want to do that apidocs.strongloop.com/loopback/#persistedmodel-upsert – Yevette

Answer 2

There are two methods in loopback one is simple upsert and second is upsertWithWhere.To insert or update based on where condition you must use upsertWithWhere method upsert only inserts the data. you are using

app.models.modelname.upsert({bbid:bb.id, saved: Saved, userid:1}
          ,function(err, res){});

instead use

app.models.modelname.upsertWithWhere({bbid:bb.id, saved: Saved, userid:1}
          ,function(err, res){});

this will solve your problem.

note: this only updates single instance.If multiple instances are returned in where condition result it will throw an error.

Permanence