Simple formatting for MySQL LAST_INSERT_ID() call?

How do I do X?
1

I have several places throughout my app in which I insert some data into my MySQL DB and then use the auto-incremented primary key when adding other data. Currently, I end my query with SELECT LAST_INSERT_ID() and use the returned value in a JS Object, e.g.:
let lastInsertID = insertData.run();
When I do that, the returned data (lastInsertID) looks like:
[{“LAST_INSERT_ID()”: 25}].
Normally, I would get the ID by calling lastInsertID[0].LAST_INSERT_ID(), but this doesn’t work because of the residual parentheses from the SQL command. Is there a simple way to call the ID (25, in this case) from that array/object?

2

Note that the following seems to work for this, but seems rather clunky.

let id = await insertData.run();
id = id[0];
for(let propName in id) {
			if(id.hasOwnProperty(propName)) {
				var propValue = id[propName];
			}
};
id = propValue;
return id;
3

Hello Dan! Could you please try to use lastInsertID[0]["LAST_INSERT_ID()"] instead of lastInsertID[0].LAST_INSERT_ID() and let us know if it works?

4

That worked! I realize now that this is again a beginner JS mistake, but hopefully this post will help someone in the future avoid this issue! Thanks, Amelia.

5

I ran into a new version of this issue for which neither of the above solutions seems to work. I run an EXISTS query which gives the following return:

Screen Shot 2023-01-09 at 6.39.02 PM
Screen Shot 2023-01-09 at 6.39.02 PM1029×110 10.9 KB

I would like to be able to reduce this result down to just “1”, but can’t seem to do so. This is stored in var res1, and calling res1[0] returns nothing. Do you know what’s going wrong here?

6

Try to get the value by using

QueryName.data[0]["EXISTS(SELECT * FROM components WHERE component_name in ('230109-DS-C-1','230109-DS-C-2','230109-DS-C-3'))"]
7

Unfortunately, that won’t work here; the parameters (‘230109-DS-C-1’,‘230109-DS-C-2’,‘230109-DS-C-3’) change with every function call, so I can’t hardcode them into my JS. Do you have ideas for a workaround here? Or is there a way to restructure my query to give a different return to Appsmith?

8

Got it. So you need to get the value of the first key of the first JSON Object returned, right?
In this case, you could try:

QueryName.data[0][Object.keys(QueryName.data[0])[0]]
1 Like
9

YES! That worked! Thank you so much!