Merge two query results / arrays / lists

[ben55]

Hi,

I’ve got a server side action that has two database queries from seperate tables, each generating an array/list, that are very similar. Examples below:

query1

[ 
{ id: 133, name: "john" },
{ id: 565, name: "joe" },
{ id: 399, name: "james" },
]

query2

[ 
{ id: 102, name: "tom" } ,
{ id: 382, name: "terry" },
{ id: 311, name: "tanya" },
]

How can I join these 2 queries/arrays/lists on server side to achieve the expected output below?
I’ve tried things like the ‘join’ action, and also tried manually setting a value that does a javasccript style query1.concat(query2), and tried on client-side too but can’t seem to work it out.

Any suggestions on how to join 2 arrays/lists together server-side? Or even client-side?

Expected output

[ 
{ id: 133, name: "john" },
{ id: 565, name: "joe" },
{ id: 399, name: "james" },
{ id: 102, name: "tom" } ,
{ id: 382, name: "terry" },
{ id: 3, name: "tanya" },
]

[s.alpaslan]

hi @ben55
you can check this link

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…

[ben55]

hi @s.alpaslan,

I did try the join function, as per the images below, but it seems to just overwrite the first query.
Set up:

This was an attempt with ‘id’, but the overwrite also happens if I specify the ‘name’ field as the column to match.
I’ve tried also leaving the ‘Match Columns’ section blank, which gives an error.

Example outputs of query1, query2, and joined_set below:

Am I missing something, or is the join function only functional if trying to match two different fields?

[s.alpaslan]

@ben55 you can use Alias in query builder

then

[brad]

Hi Ben, welcome to Wappler.

I believe what you will need to do is use the Custom Query builder and use Union to show results of both tables.

SQL UNION Operator

[ben55]

Thanks @brad
I have now used the Custom Query and UNION successfully replicate the two seperate queries and join them.
For anyone else who comes across this issue, my solution looked a bit like this

SELECT id, name
FROM table1
WHERE name LIKE '%t%'
UNION
SELECT id, name
FROM table2
WHERE name LIKE '%j%'

I wonder if it would be useful for Wappler to have some in-built functionality server-side for List/Array methods like append(), remove(), pop(), index(), sort() etc.

[brad]

Glad you have it solved. Good luck with your project!

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