Currently, Wappler provides powerful and flexible database query features, including inner joins, left joins, and right joins, which work well for combining related datasets. However, these joins rely on matching keys between tables, which makes them unsuitable for combining datasets with different roles or schemas.
For example, when working on my project, I had the following requirement:
Combine data from shareholders and directors tables, where both tables represent individuals but serve different roles. These tables do not share all fields or a direct relationship via foreign keys.
The only requirement was to merge the two datasets into a single array of stakeholders with their respective roles, preserving all rows from both tables.
Using joins for this purpose led to the following challenges:
Key Dependency: Joins require matching a key (e.g., companyId), which filters out rows that don’t have a match in both tables.
Redundancy: Joins may introduce duplicate records when keys exist in both tables but the associated data is distinct.
Inefficiency: Workarounds like multiple queries followed by manual concatenation in Wappler using scripting or external tools can be complex and error-prone.
Why UNION Is the Solution
The UNION operation allows combining two or more datasets into a single collection without requiring a direct key match. This operation is ideal for cases where:
Two datasets share similar data structures
The datasets need to be combined without filtering based on a key or creating duplicates from joins.
To combine data from tables that may contain dissimilar data structure, I create Views in the database, which also provides me the option to add column name aliases that are common across the unionised database records.
There's another option to create a Custom Query in Wappler's database query builder.
Like others have suggested you might like to have a look at the custom database query option. You can write as complex or simple union as you like.
I feel if you’re already at the stage of understanding how unions work then custom queries are far more flexible.
I can imagine including built-in support for unions might not straight forward, there’s extra complexity around making sure the columns are the same, checking if columns are cast to the same type, and a whole bunch of other things etc.
I am not that familiar with writing customs queries so this is not a viable solution.
With so many things on wappler that makes life easier to build complex apps,I find it hard to believe that I can’t simply combine 2 database queries together and create a json output. I don’t want to merge 2 sets (inner join/left/right) I want to literally combine 2 arrays without a common key.
I have tried using arrays server features but no luck.
I appreciate this is not an answer to your question and may not be relevant at all. However, in this situation - where the table structure is very similar - a good solution can be to use a single table and include a field to define the role (shareholders and directors in this case), or have a related table if there could be more than one role. This structure can make things quicker and simpler (avoid joins etc), and often avoid duplication and redundancy.
Give ChatGPT a go. Paste the table structure in, describe the query you're after and then paste the generated query code into a custom query in Wappler. You may be surprised how good it is as that.
As others already suggested, if you want to use complex functionality like sql UNION then use custom query - that's what the component is used for.
If you don't want to deal with custom queries you can use the array list options.
Create an array list, add two database queries, each of them querying your two tables. After that use two Add All In Array list steps the first one adds the data returned from the first db query and the second one adds the data returned from the second db query.
Then output the list value - you will see two results joined in a single list.
