Pagination Function

Job Writing
1

Hi All,
Would it be possible to have an SQL get statement within a function? I am currently attempting to create a function to continuously offset and limit 10,000 records from an SQL database however I am having issues in doing so:

The following code is what I am trying to accomplish within the function. There are two SQL statements to complete the task:

        sql(
        state =>
        `SELECT *
        FROM postgres.vaccinedoses
        ORDER BY patientid_fk
        ASC LIMIT 10000; `, { writeSql: true });
        
        fn(state => {
        const vaccinationevents = state.data;
        console.log("Fetching Vaccination Event Data Limit 10000...")
        return {...state, vaccinationevents};
        });
        
        sql(
        state =>
        `SELECT *
        FROM postgres.vaccinedoses
        ORDER BY patientid_fk
        ASC LIMIT 10000 OFFSET 10000 ; `, { writeSql: true });
        fn(state => {
            const newVaccinationEvents = state.data;
            console.log("Fetching Vaccination Event Data Offset 10000...");
            // Append new data to existing vaccinationevents array
            const updatedVaccinationEvents = [...state.vaccinationevents, ...newVaccinationEvents];
            return { ...state, vaccinationevents: updatedVaccinationEvents };
        });

Attempt at function:

        function fetchDataWithOffset(offset) {
            const sqlQuery = `
                SELECT *
                FROM postgres.vaccinedoses
                ORDER BY patientid_fk
                ASC LIMIT 10000 OFFSET ${offset};`;
        
            sql(
                state => sqlQuery,
                { writeSql: true }
            );
        
            fn(state => {
                const newVaccinationEvents = state.data;
                console.log("Fetching Vaccination Event Data Offset " + offset + "...");
                // Append new data to existing vaccinationevents array
                const updatedVaccinationEvents = [...state.vaccinationevents, ...newVaccinationEvents];
                return { ...state, vaccinationevents: updatedVaccinationEvents };
            });
        }
        
        
        // Call the function for each offset
        fetchDataWithOffset(1);
        fetchDataWithOffset(2);
  • This image highlights the error I am receiving

Screenshot 2024-05-08 153506

Any assistance is greatly appreciated.

2

@DanglebenC i will share some suggestion shortly but to help me understand the problem, Do you want to fetch all data when the workflow run or do you want to fetch data using the offset the next time the workflow run ?

Because if you want to get all data, why not fetch all data at once ?

3

If I’m understanding correctly, I’d try something like this:

// add a custom function to state
fn(state => {

  const sqlFun = (offset) => {
    console.log("Fetching Vaccination Event Data Offset " + offset + "...");
    return 'SELECT * FROM postgres.vaccinedoses ' +
      'ORDER BY patientid_fk ASC LIMIT 10000 ' +
      `OFFSET ${offset};`;
  }

  return { ...state, sqlFun }
});

// then use it! twice!
sql(state => state.sqlFun(1), { writeSql: true });
sql(state => state.sqlFun(2), { writeSql: true });

Does that structure make sense?

As an aside… @mtuchi , could those last two calls be replaced with:

sql($.sqlFun(1), { writeSql: true });
sql($.sqlFun(2), { writeSql: true });

or is that not quite how the new $. helper works?

4

I would like to get all the data however there are 80,000+ records. Offsetting and limiting would increase efficiency and reduce the chance of errors if any.

5

Yes the structure makes sense. That works.
Using the $. helper throws an error: $ is not defined.

6

Ah that’ll be a bug in the new $ helper! I’ll open out an issue for that and get it sorted - thanks for trying it out @taylordowns2000 !

7

Here is the proposed approach we discussed on the call

const sqlFun = (offset) => {
  console.log("Fetching Vaccination Event Data Offset " + offset + "...");
  return (
    "SELECT * FROM postgres.vaccinedoses " +
    "ORDER BY patientid_fk ASC LIMIT 10000 " +
    `OFFSET ${offset};`
  );
};

fn((state) => {
  state.allResults = [];
  state.offsets = [1, 2, 3];
  return state;
});

each(
  "$.offsets[*]",
  sql(
    (state) => sqlFun(state.data),
    { writeSql: true },
    (state) => {
      state.allResults.push(state.data);
      return state;
    }
  )
);