Joining Data From Multiple Sources

With connectors for REST and GraphQL APIs, and all major databases, Appsmith is great for joining data from multiple sources and building a central view of the data. In an ideal world, all this data would be organized in a single backend, but in reality, most enterprises have multiple SaaS products, internal databases, spreadsheets, etc. The data is related, but loosely. It's not like in a relational database that maintains referential integrity. Instead, you have emails for IDs in one system and a customer ID in another, orphaned or duplicate records, different names for the sync field(s) across systems, etc. 

With Appsmith, you can build a UI to connect to all those datasources directly, and join the data with JavaScript on the client-side. In this guide, we'll cover several different ways of joining data, and how to optimize the joining for larger datasets. 

This guide will cover:

• Joining Two Tables in SQL
• Using Custom Columns in a Table Widget
• Joining SQL response with an API response
• Using find() in JavaScript to match rows by ID
• Using forEach() to create an object to lookup values

Let's get this this data together! 

JOINING Two SQL Tables

In this first example, we'll be joining data from two tables using a JOIN to perform the work on the database server. This should be the preferred approach, any time this is an option, so I wanted to cover it before going on to the client-side joining that we'll be doing with JavaScript in the next section. 

Setup

Two separate SQL Queries:

SELECT * FROM sales LIMIT 100;
SELECT * FROM "customer_order" LIMIT 100;

Here, we have a sales table in Supabase, and each row has an Order ID, but no Customer ID. Each one is a separate query at this point, but we want to join the data and display it as a single table. Since both tables are in the same database, the best approach is to just use a JOIN statement. 

SELECT sales.*, "customer_order"."Customer ID"
FROM customer_order
JOIN sales ON CAST(sales."Order ID" AS INT) = customer_order."Order ID"
LIMIT 100;

Let's take a closer look:

SELECT sales.*, "customer_order"."Customer ID"

The Customer ID is in both tables, and I don't want to get back two copies of it. So sales.* selects all columns from only the sales table, and then in the customer_order table, only the Customer ID is selected. 

Note: Double quotes are used around field and table names in Postgres, and single quotes for strings. More info in this guide. 

JOIN sales ON CAST(sales."Order ID" AS INT) = customer_order."Order ID"

In this case, both tables have an Order ID, but the Sales table is storing the data as a varchar and the customer_order table is using an Int. This is not the best setup, but in real-world situations, you often have to work around these kinds of issues and have no control over changing the backend. 

The new query returns both tables joined, with only one version of the Customer ID. 

Joining SQL with an API Response

Ok, we can display the customer email with each order now, but what about their name and shipping info? This time the data is coming from an API instead of another SQL table. For this example, I'll be using Mockoon's API Playground with data from their /customers endpoint. 

Here you can see the SQL join table displayed above the /customer's API. 

There's no way to do this join on the server side since it's two totally different datasources. In this case, we need to use a little JavaScript and merge data on the client side. 

Custom Columns

Select the Table widget and click the + Add new column button, and then name the column Customer Name. Then click into the column settings. 

Here we can use mustache syntax {{ }} to write JavaScript in the Computed value field.  

First, I'm going to show you how NOT to do it, because I want to call out the performance issue with this method. You may be tempted to use a find() method here, and it would work, but it would be terrible for performance on large datasets. 

{{getCustomers.data.find(c=>c.email == currentRow["Customer ID"]).name}}

The problem with this approach is that a find is being performed on every row from the order side. And each time, it has to start over and loop through all the rows until the correct customer ID is found. 

A much better approach is to loop through the orders only once, creating a lookup object to easily access the customer data using key:value pairs. 

JSObjects and Variables

First we'll create a new JSObject and add a function to loop over all the customer orders. We'll need somewhere to store the lookup object, which could be the Appsmith store (like browser local store), or we can use a page variable in the JSObject. Since mutating the variable in memory is a lot faster than updating the Appsmith store, so we'll go with a variable. 

export default {
	
	customerObj: {}, // lookup object to store customers by id
	
	createCustomerMap () {
		const customers = getCustomers.data;
		customers.forEach(c => this.customerObj[c.email] = c);
		return this.customerObj
	}
	
}

This returns a nested object, where each customer can be accessed directly by using the email as the key. 

The value at customerObj["Adrianna_Denesik@yahoo.com"] is the entire customer object for that user. Now each customer can be looked up easily, without looping over the full set once for every order.

Head back to the table widget and update the Computed value to use this new object. 

{{JSObject1.customerObj[currentRow["Customer ID"]].name}}

This way is much more performant for large datasets. 

Conclusion

With Appsmith's ability to connect to nearly any API or database, it's the perfect frontend to bring together all your data from various sources. With just a little JavaScript, you can easily merge tables from multiple datasources into a single UI and create a 360 view of your data.