Combining two different table queries into one output for use in a repeat

[mgaussie]

Hey all,

I’ve not kept up to date each week with updates and I see there has been a fair amount of updates to queries, and the database manager.

Is there now a possibility to combine two DB Queries into a single output that’s useable in a repeat? If there is (awesome) what would be the best approach, or link to documentation I can study?

[Sorry_Duh]

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 joi…

[mgaussie]

Thanks @Sorry_Duh - I thought of the join function, but unfortunately there isn’t a ‘matching’ id number between the collections we wish to join, while the info is fairly similar, the items are not aligned in anyway so I was wondering if another feature has been developed that could solve that.

[Teodor]

So following what logic they should be joined? Can you share sample output from the two queries and the result you need?

[mgaussie]

Hey Teo,

So what we have is a transaction history for a single web3 wallet address.

Their transaction history is stored in two different tables. The items they are buying, are entirely different from each other so there is no common id, or common link between the two.

I want to output the transaction history of both tables into one output I can then show on the F/E.

Example:

TXN Table 1:

id
item_name
description
price
txn
item_id (linking to that specific purchases table)

TXN Table 2
id
collection_name
description
price
txn
item_id (linking to that specific purchases table)

the item_id is not a common link between the two, as they themselves are separate tables with unique information.

So the goal here would be to have a single output:

id
name (which would be either item_name or collection_name)
description
price
item_type (e.g. type one, or type two)
date etc.

[Teodor]

Ok but then how do you know which record from table 1 should be joined with which in table 2?
Or do you just want to list all records from table 1 followed by all records from table 2 in a single output?

[mgaussie]

Correct - they aren’t joined by anything, I would just like to be able to show records from both tables in a single output

[Teodor]

What database are you using?

[mgaussie]

MariaDB

[Teodor]

You can use sql UNION for this https://www.w3schools.com/sql/sql_union.asp
So setup a custom database query in your server action and use it there.
Example:

SELECT id, item_name AS name, description, price, txn, item_id FROM Table1
UNION
SELECT id, collection_name AS name, description, price, txn, item_id FROM Table2

[mgaussie]

ty Teo - i’ll try this out. Much appreciated!

[mgaussie]

this worked great, thanks Teo. Perhaps a Union visual implementation in Wappler would be cool in the future!

[sylvainbaron]

Hi,
How did you proceed?
I have exactly the same need.
Many thanks

[Teodor]

Please check the reply marked as a solution.

[Teodor]

That’s already possible using the Server Connect Array List component:

Working with Server Connect Array Lists

Intro The Server Connect Array List component allows you create and manipulate Array Lists easily with Server Connect actions. You can add items to an array, join multiple sources, remove and set arbitrary items, keep simple or complex objects or collections in an Array List. It’s also easy to use it as a string builder, when you need multi lines strings. Basics In order to be able to add items to an array you first need to define the array. The Array List actions are located in the Data Man…