Execute GraphQL query with variables based on the currently displayed data from SQL

App Building
, ,
1

Hey, I'm trying to achieve the following for a proof of concept:

  • Fetch data from a SQL query and populate a table (easy)
  • After fetching the data from SQL, I want to execute a GraphQL query, passing in the emails from the currently displayed table data as a variable to the Graph - this is so we don't overfetch data from Graph, and only request what's needed
  • I then want to merge the SQL and GraphQL data based on records ids
  • When the user navigates to the next page, it executes the Graph query again with the new emails

I feel like I'm close, but I can't get the event handlers working correctly.

Here's what I have:

sql query

select id, email from users

graphql query

query($emails: [String!]) {
  users(where: {email: {in: $emails}}) {
    records {
      id
      name
    }
  }
}

I have some JS code for getting the emails currently displayed:

const table = table1;
const allTableData = table.displayedData;
  
const currentlyDisplayedData = allTableData.slice(table.paginationOffset, table.paginationOffset+table.pageSize);
    
const currentlyDisplayedEmails = currentlyDisplayedData.map(row => row.email);

And finally a transformer for merging the SQL and Graph data - this should return the id, email and name to display in a table.

const sqlData = {{formatDataAsArray(sql_user_id_email.data)}}
const graphData = {{graphql_user_email.data ? graphql_user_email.data.users.records : []}}

const mergedData = sqlData.map(sqlRecord => {
  const graphRecord = graphData.find(graphRecord => graphRecord.id === sqlRecord.id)
  const name = graphRecord?.name || ''
  
  return {
    ...sqlRecord,
    name
  }
})

return mergedData

What should the flow/event triggers look like for this?

So in summary, when the user first loads the app, I want it to execute the SQL query -> fetch GraphQL data using the emails from the SQL query (but only those displayed), merge the data and show this in the table.

Every subsequent page navigation should refetch Graph with the new displayed emails, and call the merge data transform again.

2

Figured this out, if it's useful to anyone.

I enabled server side pagination on the table component:

sql_user_id_email - setup to run automatically when inputs change

select id,email from users where (COALESCE({{users_select.value}}, '') = '' OR users.email = {{users_select.value}}) offset COALESCE({{offset}}, {{table1.paginationOffset}},0) limit {{table1.pageSize}}

graphql_user_name - set to be manually triggered only

query($emails: [String!]) {
  users(where: {email: {in: $emails}}) {
    records {
      id
      name
    }
  }
}

On success of the SQL query, it calls the below JS query, which gets the currently displayed emails and calls Graph passing these in as a variable. It then merges the results, and this is used as the table data.

fetch_and_merge - set to be manually triggered only

async function runQuery() {
  const table = table1
  const sql = sql_user_id_email;
  const currentlyDisplayedEmails = sql.data.email
  
  return Promise.resolve(graphql_user_name.trigger({
    additionalScope: { emails: currentlyDisplayedEmails }, // This is where we override the `emails` variable
  }));
}

const graphData = await runQuery()
const graphUsers = graphData.users.records

const sqlData = formatDataAsArray(sql_user_id_email.data)

const mergedData = sqlData.map(sqlRecord => {
  const graphRecord = graphUsers.find(graphRecord => graphRecord.id === sqlRecord.id)
  const name = graphRecord?.name || ''
  
  return {
  ...sqlRecord,
  name
}})

return mergedData
2 Likes