Data Transformation (Join) returning empty objects

Wappler Version : W4 B9
Operating System : Mac M1
Server Model: NodeJS
Database Type: MySQL
Hosting Type: AWS Docker

Expected behaviour

What do you think should happen?

This may be by design but I feel that it is more of a bug…

When a DB query returns a field as null, the join should output the field as null

Actual behaviour

What actually happens?

Empty fields are returned as empty objects:
image

This is the result from the original query:
image

How to reproduce

  • Detail a step by step guide to reproduce the issue
  • A screenshot or short video indicating the problem
  • A copy of your code would help. Include: JS, HTML.
  • Test your steps on a clean page to see if you still have an issue

Create 2 database queries and join based on fields. Use a Set Value to output the result
image

The main problem is the formatters I normally use to detect presence or not of a value don’t work for objects so I am currently forced to workaround with .keys().hasItems() as part of a ternary to apply a default value.

The fields present in the query that produce the empty objects are a mixture of numeric values (INT), datetime or strings of some description (usually VARCHAR) - it seems to not matter on the data type of the DB column.

This is proving to be more problematic than first thought. The workaround doesn’t work because when a string is actually returned, the formatter .keys() throws an error as it isn’t available for strings. Any suggestions or is there any chance of getting it fixed quickly?

Hey @patrick,
I’ve been having a little play with the join function and seem to have a solution:

join: function (options) {
        let collection1 = this.parseRequired(options.collection1, 'object'/*array[object]*/, 'collections.join: collection1 is required.');
        let collection2 = this.parseRequired(options.collection2, 'object'/*array[object]*/, 'collections.join: collection2 is required.');
        let matches = this.parseRequired(options.matches, 'object', 'collections.join: matches is required.');
        let matchAll = this.parseOptional(options.matchAll, 'boolean', false);
        let output = [];

        for (let row1 of collection1) {
            newRow = {};
            for (let column1 in row1) {
                newRow[column1] = row1[column1] == null ? null : clone(row1[column1]);
            }

            for (let row2 of collection2) {
                let join = false;

                for (let match in matches) {
                    if (row1[match] == row2[matches[match]]) {
                        join = true;
                        if (!matchAll) break;
                    } else if (matchAll) {
                        join = false;
                        break;
                    }
                }

                if (join) {
                    for (let column2 in row2) {
                        newRow[column2] = row2[column2] == null ? null : clone(row2[column2]);
                    }
                    break;
                }
            }

            output.push(newRow);
        }

        return output;
    }

I’m sure you’ll find a more elegant solution but iterating the source row rather than simply cloning it and adding a ternary if null seems to resolved it for me.

The bug is in the clone function, it should just return null. This probably affects more then just the join. Here the update, unzip the file in lib/core.

util.zip (1.4 KB)

Thanks Patrick. That looks like it’s working perfectly. Appreciated as always!

Hey Ben,
Just curious, what’s the use of this join action step, compared to the INNER/LEF/RIGHT join with SQL?

You can’t use joins for data sources different than databases - API responses, arrays etc… Also you can’t join tables which are not in the same database.

1 Like

As @teodor says, it’s most useful for combining data from non-connected sources. My customers have their own database servers, which they own but we create a dynamic connection to, while we run the authentication and some other bits from a central DB. It allows us to run queries on each and then combine as though they were joined within the DB query.

1 Like

Also you can’t join tables which are not in the same database.

Doesn’t this depend on the query permissions attached to each separate database query?
And the queries produce a temp table from db1.orders and another one for db2.customers before carrying out a filter query on the combined db1.orders & db2.customers temp values?

mysql> select id, order_date, amount
from (
select id, order_date, amount
from db1.orders
union all
select id, order_date, amount
from db2.cusomers)
temp
group by id, order_date, amount
having count(*)>1;

Or what don’t I know?

Not all DBs are necessarily on the same physical server - therefore it’s not always a permissions issue

Exactly.
Which was actually what I meant. From the example I read about that clients had databases running on their own servers.

My customers have their own database servers, which they own but we create a dynamic connection

And following up when you said about, essentially, Permissions – ability to connect and query remote databases according to each use case.

while we run the authentication

Or what else do you think is instructive to add?

This has been fixed.