Looping through array

Queries and Resources
1

Newbie question and I know the answer must be annoyingly obvious but this loop is giving me a syntax error when it the loop starts. tableOrgNodes is a multi-select table and the data in the array is [101026,148152,594611]. When I run the query I get the following error message:
syntax error at or near "101026"

DO
$do$
declare x INTEGER;
BEGIN
   FOREACH x IN {{tableOrgNodes.selectedRow.data.map(row => row.id)}}
   LOOP
   --INSERT ........
   END LOOP;
END
$do$;

I'd be very grateful for any help.

2

Heya :wave:

What are you trying to do exactly? Could you descripe the workflow you're trying to build?

3

Hey there. I'm trying to execute an insert loop using selected rows from a multi-select table. I commented out the insert statement to isolate the error and the loop doesn't run because of some syntax error which is one of the values in the array or near one of the values in the array.

4

Gotcha, kinda.

Haven't worked with foreach in that context inside retool.

I would abstract the looping logic into it's own JS query and trigger the insert from there.

May not be the answer you're looking for but would solve your issue.

Would that work for you?

5

Anything would work for me if it works :grinning: The thing is I have zero experience of JS so I wouldn't know where to begin.

6

:joy: You're on the right path, welcome to the gang.

Let me try and spec this out:

  1. Create a new JS query (e.g. loop_insert)
(async () => {
  var selectedRows = tableOrgNodes.selectedRow;

  for (let i = 0; i < selectedRows.length; i++) {
    await new Promise((resolve) => {
      insert_db.trigger({
        additionalScope: {
          id: selectedRows[i].id
          // add all the fields you need to update
        },
        onSuccess: (data) => {
          resolve(data);
        }
      })
    });
  };
})();
  1. Create a new DB query (e.g. insert_db). You can access the scope passed down of the JS query via {{id}}.

Let me know if that works out of the box for you. If not, share a few screenshots of the queries you created and we'll try to get it working.

2 Likes
8 
(async () => {
  var selectedRows = tableOrgNodes.selectedRow;
  var bndlid = tableBundles.selectedRow
  for (let i = 0; i < selectedRows.length; i++) {
    await new Promise((resolve) => {
      insert_node_bundle.trigger({
        additionalScope: {
          node_id: selectedRows[i].id,
          bundle_id: bndlid[i].id
        },
        onSuccess: (data) => {
          resolve(data);
        }
      })
    });
  };
})();

insert_node_bundle

INSERT INTO admin.bundles_nodes (node_id, bundle_id) VALUES (node_id, bundle_id);

Thanks @minijohn This is what a tried. It runs without any errors which is an unfamiliar experience BUT nothing was inserted into the table :frowning: Can you see what I did wrong?

1 Like
9

a) Change the query to:

INSERT INTO admin.bundles_nodes (node_id, bundle_id) VALUES ({{node_id}}, {{bundle_id)}};

b) If that doesn't do the trick, try using the GUI version of the db query.

(don't pay attention to the arrow)

2f59311-Screen_Shot_2020-05-26_at_10.43.39_AM
2f59311-Screen_Shot_2020-05-26_at_10.43.39_AM1920×945 107 KB

10

tried the curly brackets in SQL, tried GUI mode even with fixed values and nothing was inserted. I think the query isn't being triggered

11

I tried this which I adapted from the retool docs which did trigger the insert query but never stopped. It kept iterating way past the 3 rows which were selected in the table. Huge leap forward but still not there yet.

var selectedrows = tableOrgNodes.selectedRow;
var bndl_id = tableBundles.data.id
function runQuery (i) {
  if (i >= selectedrows.length) {
    console.log('Finished running all queries');
    return;
  }
  var data = selectedrows[i];
  console.log('Running query for row', data);

  insert_node_bundle_ids.trigger({
    additionalScope: {
      node_id: data,
      bundle_id: bndl_id
    },
    // You can use the argument to get the data with the onSuccess function
    onSuccess: function(data) {
      runQuery(i + 1);
    }
  });
}

runQuery(0);
12

The problem seems to be that selectedRows is undefined. I logged the output of selectedRows.length and it says undefined.

13

Hi @leclep! Happy to take a look at this with you. For your latest question, if you open up tableOrgNodes.selectedRow in the left panel, what do you see there? We may need to grab something like tableOrgNodes.selectedRow.data specifically if it's returning an array of selected row objects!

910×176 6.37 KB

14

Hi Victoria and apologies for the late response. I didn't get an email notification that there had been a response and haven't checked in here for a few days. Anyway, I did get this to work in the end but having the the trigger() inside the for loop makes it really slow so I have been trying to work on an alternative option which is:

var bundlestudy = new Array();
var bndl_id = tableBundles.selectedRow.data.id;
function runQuery (i) {
  for (var i = 0; i < ListboxStudies.selectedItems.length; i++) {
    var valueToPush = {};
    var study_id = ListboxStudies.value[i];
    valueToPush[0] = study_id;
    valueToPush[1] = bndl_id;
    bundlestudy.push(valueToPush);
    //console.log('test',i, valueToPush[0], valueToPush[1], bundlestudy[0][i]);
  }
}
runQuery(0)

await runQuery(0);
  query116.trigger({
  	additionalScope: {
   	bundlestudy: bundlestudy,
    },
  });
  
bundlestudy.transposed = new Proxy(bundlestudy, {
    get: (arr, col) =>
        +col >= 0 ? new Proxy({ length: bundlestudy.length }, {
                        get: (obj, row) => +row >=0 ? arr[row][col] : obj[row]
                    })
        : col == 'length'   ? arr[0] && arr[0].length
        : col == 'original' ? arr 
        : undefined
});

var t = bundlestudy.transposed;

await runQuery(0);
  insertBundleStudy.trigger({
  	additionalScope: {
   	bundlestudy: t,
    },
  });
// This is the output of the transposed array:
console.log(t[0][0],t[1][0]) //1a603b0e-a37e-44df-95dc-dce3ad51ad82 10
console.log(t[0][1],t[1][1]) //6b043372-8668-443b-9033-3ddf838be37d 10
console.log(t[0][2],t[1][2]) //5c5378fc-05c6-4147-872d-6e2d4bc3324e 10

// This is the insertBundleStudy query using the GUI method:

{{ [{study_id:bundlestudy[0]}, {bundle_id:bundlestudy[1]}] }}

I thought by transposing the array from this structure [ [s1, t1 ] , [s2, t2 ] , [s3, t3] , [sn, tn ] ] to this structure [ [ s1, s2, s3, sn] where s i= study_id and t =bundle_id I could then reference the array using bundlestudy[0] and bundlestudy[1] but it produces the following:
" !error "insert into "admin"."bundle_study" ("bundle_id", "study_id") values (DEFAULT, $1), ($2, DEFAULT) - invalid input syntax for type uuid: "{"0":"1a603b0e-a37e-44df-95dc-dce3ad51ad82","1":10}".
If you could help with this I'd really appreciate it. Thanks in advance.

15

This issue is resolved and here's how.

jsMakeBulkBundleStudyArray.trigger({
    onSuccess:function(data) {
      insertBundleStudy.trigger({
        additionalScope: {
          dataArray: data
        }
      })
  }
});

triggers this

 var newData = [];
//ListboxStudies.value.forEach(num => console.log(num));
  ListboxStudies.value.forEach(num => {
    newData.push({
    study_id: num,
    bundle_id: tableBundles.selectedRow.data.id
  })
});
return newData

and then triggers a GUI Bulk Insert with this

{{dataArray}}

as the Array of Records to Insert.

16

@bradlymathews gets all the credit for the this solution from his response to this thread How do I add multiple rows to a table in postgresql

2 Likes
17

Most excellent! Thank you for updating this post with your solution so others can find it, just like you found Brad’s (thanks, Brad) :heart:

18

Don't mention it. I've been a bit frustrated at times not being able to find answers in the documentation so rather than just complaining about that (which I have done) I felt this was more constructive. I do think it would be good though to at least reference relevant solutions from the respective sections of the documentation or pull them in and present them in the document itself. Just a suggestion. I know it's a huge task but the better the documentation, the fewer requests for support the support team will get.

2 Likes