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:
This is the result from the original query:
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
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.
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.
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.
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;
