Hi All,
i’m using wappler 5.4.0, node js and MySQL. I have a paged query using Distinct, and configured to return nested query. Executing the query from the query builder returns 2 records, however, the API returns 4 records and ignores DISTINCT statement. Follows the query builder results:
Follows the API results:
Noted that the API is stating that there are total of 2 records, however, data contains 4 records (not 2).
Would you please advise with workaround if any till a fix is addressed?
We discovered this issue a couple of days ago as well. Its logical in a sense that the duplicated rows might have different sub/nested-query outputs.
We ended up using a custom query for our purpose.
Not sure what the exact logic Wappler uses to build the final output. But if this is a bug, it would be great to have distinct work with sub-queries.
I think this is why I implemented the following inside a RunJS action:
const unique = [...new Map(arr.map((m) => [m.NAME-OF-COLUMN-PROVIDING-UNIQUENESS, m])).values()];
return unique;
arr = the array you want unique items from
The output of the RunJS is the array you can then use.
How is the relation between the main table and the sub table?
For the DISTINCT statement in SQL every output column is checked, when a single column is different it is seen as not equal. With nested tables we add the primary/foreign key to the output which is having a relation with the sub table to be able to join the results together. If that key has different values in the resultset it can result in extra rows.
With or without different values, the main table DISTINCT does not work. It always returns multiple results, if main table has multiple rows, with same value in sub table array.
I notice in the screenshot different table prefixes, does the main query also use joins?
In my use case, main query does have joins. And it looks like so in the original question as well.
Could you send me the json of the action file where this goes wrong.
Here a small update, it should prevent adding primary key columns from joined tables when they don’t have sub tables.
dbconnector.zip (3.5 KB)
Hey @patrick
If this is the update that came through asset updater (dbconnector.js doesn’t have dates or versions in it), it breaks for the following:
SELECT DISTINCT users.user_id, users.username, users.first_name, users.last_name
FROM users
INNER JOIN user_roles ON user_roles.user_id = users.user_id
WHERE user_roles.host_id = :P1 /* {{$_SESSION.host_id}} */ AND (users.username LIKE :P2 /* {{$_GET.q}} */ OR users.first_name LIKE :P3 /* {{$_GET.q}} */ OR users.last_name LIKE :P4 /* {{$_GET.q}} */ OR users.cell_phone_number LIKE :P5 /* {{$_GET.q}} */)
When I run this before the update, I receive the proper list, but with the update, I get dupes.
