Join Results of 2 Data Collections

Intro

Using the Data Transformations options you can easily join the results of two different data sources such as - a database query and an API response, the results of 2 database queries from 2 different databases, or any other array data.
In our example we will show you how to join the results of 2 database queries from 2 different databases.

Data Transformation

We have two database queries using two different database connections, so we can’t join them using the query builder. We can join them using the Data Transformations available in Server Connect.

Data Sources

This is the first database query:

And this is the second one:

If we run this server action, they return the following data:

The first query returns orders (order ID and product ID) and the second one the product details. We want to join them so that they are returned together as one data source.

Join Data Sources

Add a new step in your API Action:

Open the Data Transformations Category and select Join:

Here we need to select our two data collections.
Click the dynamic data picker for the first one:

Select the first query and click the select button:

Do the same for the second data collection:

Select the second query and click the select button:

Now we need to select columns to match them by:

Enter the names of the columns of the first and second collections which you want to match by:

Make sure to enable Output for the join step:

And then disable the output for the two query steps, as we don’t need them to be outputted.
Save your API Action:

And you can see the results, our two collections are joined:

18 Likes

Wow, this is going to open up a whole new world for me, thank you, love it.

1 Like

I have done this using ‘views’ in Navicat in the past. Great to see this being able to be done right in Wappler. Nice feature. Thank you.

Views are possible when you are using the same database, the same is already possible in the query builder.

Using this option you can join the results from different data sources - i.e. a database query and an API response, or two queries from different databases, or an array and another array :slight_smile:

4 Likes

Just a concern: I do not see any posts on the documentation of the other three options in Data Transformations section. Why are you guys still not working on the docs support of Wappler. I’m sure this was in no way an urgent feature. It would have been much better if you guys could have prepared the other docs too and released everything after a week or two.

I was happy to see some other features getting their docs along with release many of the latest releases, but with Stripe and this, its back to missing docs problem.
I don’t use Stripe, so did not raise this concern there as I don’t know anything about it. But this looks like a great set of features, with unfortunately missing docs.

Hello @George @Teodor

Being able to add more data collections might actually be better. Is it possible to make this component as multiple data collections? Is there a technical reason that there are only 2 datasources right now?

1 Like

@sid

1 Like

Thank you. :slightly_smiling_face:

I assume its there for the sake of simplicity.
You can just reuse it again as 1st source in the next step and add third collection as 2nd source.

Creating a custom extension to make this more powerful should not be a big task either.

Yes you are right. It can be used this way, but as you said, it would be much more useful to have multiple data sources.

You start with two data sources - and it creates a new merged data source that you can again merge with other data sources - so you can repeat it as many times as you wish.

You are just manipulating the data in various steps till you get it right. So adding/removing columns merging various source etc. - See it as a data transformation pipeline.

Yes @George

However, I thought it would be more comfortable to use the component 1 time in the UI. We already can reach the result we want in this way, there is no problem at all :slight_smile:

These new options are honestly going to make a massive difference in my life.

Having workflows where I may save data into my database for say products chosen in a shopping cart, and then need to get availability of those products from a third party API, and date options from a second API, and then pass all that data in a single step to a payment provider is currently a bit of a pain, while these tools are going to cut down multiple step procedures to far more streamlined ones.

Thank You Wappler

3 Likes

This is a great new ability, Join Results of 2 Data Collection.

It does make me curious about how all of you Developers that develop such products & order details apps, whether clothes or restaurant orders normally store the pertinent categorical data.

How often do you store the broad product category tables in a SEPARATE DATABASE from the Order Details tables?

I’ve copied JOIN queries from different Tables that all exist in the same database to another Results table in the same database or I’ve saved the results into another database altogether.

But I never had to query a table of products from a separate database than my query for the matching order details.

Do developers here have example stories of Querying more than ONE DATABASE source to make a Join for In common value tables?

1 Like

I don’t think it’s something that happens often.
It’s however often to use some external API or database and want to join it with some data coming from your database.

For this post Two data sources, one output

I was using data from a corporate database to retrieve real time job numbers, etc. but the core of the app used its own set of tables.

2 Likes

@NewMedia, it depends on you scenario. Take an example like this.

You have a travel company that sells boat tours, surfing trips, and volcano walks in a country like Hawaii, they are a full service operation that you can also book your flights through, as well as book the any of the 3 tours online.

The flight availability is held by the Airline, as well as the price of the flights, so you need a way to get that data, either by dealing with the single airline itself, or more commonly dealing with an API provider that already deals with all the airlines and stores all the information for you to access in their API.
In my example someone like Amadeus would hold all this information.

The tours themselves once you get to Hawaii may be operated by many third party companies, so lets say the surfing tours are operated by Company A, and the boat tours by company B.
You need a way to find the latest pricing as well as availability from each company, most of these companies may be smaller, self owned businesses and would not have their own APIs, however they would more than likely deal with a booking system, so lets say one uses Bookeo, another uses Rezdy, and a third uses Activitar.

Now the person who is booking through your website also needs a place to sleep while in Hawaii, so you may also sell accommodation at the same time, and again some accommodation could be a big hotel chain, and others may be a bed and breakfast, most of these companies would also not have their own APIs in place, however they would have some type of booking system, such as Siteminder with Booking Button or Nightsbridge. You again need their pricing and availability to make a booking.

Now on your own website, the person selects some dates to travel, they are presented some options, like do they want to do 1 tour only, or many tours, how many people are going, what are their ages, do they want to include flights, do they want to include accommodation.
If they chose your full service then you would need to pull the data from possibly 8 different API sources into a single source to pass to either PayPal or Stripe, and save all that data back to your database at the same time.

This is one example of where something like this can make your life far easier, not to even mention you could also add 2 more parameters to all this complexity, such as currency and language, there are just so many sources, that need to form a single transaction in the end of the day.

Example 2, which is less common, but there are instances I have found where a single company has more than one website, that both sell the same product, with slight variation, normally this is when one product is aimed at a local market, and the other is more international facing.

So if I were selling mobile phones in India, and that was my local country, I may be able to sell the handset as well as the data and airtime contract in a single package, but if I were selling the handset to a person in America, I could only sell the device itself and not the contract.

In such a case I would possibly have 2 websites.
The local website database would be the “Main” database, holding all the handset information and specifications as well as image links, this would also hold all the contract information needed.
The international site, does not need to replicate all the product data, it pulls it from the “Main” database, and ignores all the contractual tables, but uses it’s own local database to store it’s own data.

Both of these examples I have provided are real world examples that I have had to deal with on more than one occasion myself.

4 Likes

Amazing !

Gee, Paul, Hmmm— that all sounds so farfetched!
:zap: :city_sunrise: :ok_hand:

Thank you!
I have my newest app all prototyped now!

1 Like

Not farfetched at all, just take a look at Expedia, Amadeus, in fact most OTA’s (Online Travel Agents), and fortunately for all of us, pretty easily achievable with Wappler, even before this update.

2 Likes