Build a database query based on multiple exported values

Imagine a scenario where you need to retrieve information from a database using a single SQL query – for performance reasons since a database is, after all, a scarce resource. The query must contain values from different exported records within that single statement.

How can you accomplish combining these records in integrator.io? Answer: A simple JavaScript function in the right place. 

In the steps outlined below, we will build two lists of values:

• A list of concession numbers
• A list of branch numbers

The values will be retrieved from an exported file and separated into the two lists by a script. Finally, we will query a database to return one or more records corresponding to the values in the lists.

1. Write the JavaScript function

The first step is to create a function that loops through the data, or “payload,” and builds the two lists: listOfConcessionNumbers and listOfBranchNumbers. Once those lists are built, they are placed back into the payload, to be referenced later when querying the database.

function blockQuery (options) { 
  var listOfConcessionNumbers = "" 
  var listOfBranchNumbers = "" 

  options.data[0].FileDetailRecord.forEach(function(item){
  if(listOfConcessionNumbers.length !=0){ listOfConcessionNumbers +="," }
  listOfConcessionNumbers = listOfConcessionNumbers +"'"+ item.ConcessionNumber +"'"

  if(listOfBranchNumbers.length !=0){ listOfBranchNumbers +=","}
  listOfBranchNumbers = listOfBranchNumbers +"'"+ item.BranchNumber +"'"})

  options.data[0].listOfConcessionNumbers = listOfConcessionNumbers 
  options.data[0].listOfBranchNumbers = listOfBranchNumbers

  return{ data: options.data, errors: options.errors, abort:false} 
}

2. Build the query, in the lookup step

The next step is to create the SQL query, keeping in mind that the lists (as comma-separated string variables) will be readily available at this point to be used during the lookup step:

SELECT LOCATION_ID 
  FROM LOCATIONS
  WHERE CONCESSION_NUMBER in ({{{data.listOfConcessionNumbers}}}) 
    AND RIGHT (LOCATION_ID,4) in ({{{data.listOfBranchNumbers}}}) 
    AND LEFT (LOCATION_ID,2) = STORE_PREFIX;

The important aspect to note in this query is the correct handlebars syntax to reference the lists built in the JavaScript function, in particular the usage of {{{three curly braces}}}.

3. Trigger the function before the database export

Finally, we have to trigger the execution of the JavaScript function within our flow. Remember that the lists have to be available in the payload, prior to executing the lookup. This means we have to execute the list at an optimal place, where we have all the data we need and, of course, prior to the database query.

In our case, this is at the step just before the lookup step (the FTP transfer, above). Here we select the hook (  ) to call blockQuery() and process the records returned by the FTP step, before they are passed downstream.

In addition, it’s important to apply the correct response mapping (  ) after the query has executed, to ensure the lookup data is available in subsequent steps.

Key notes & considerations

Here are some important things to keep in mind when building a flow like the one described above:

• The database should return a record for each item in the list, plus an empty record when one is not found
• Pay close attention to the number of items allowed in a list, according to the guidelines or best practices of the database being used