MySQL/MariaDB NOT IN Statement Not Working With Array

[TMR]

Wappler Version : 6.3.3 stable
Operating System : Win11
Server Model: NodeJS
Database Type: MariaDB
Hosting Type: Dedicated Server (Windows)

Expected behavior

There are 16 UIDs in total inside a table. The array: arrUIDs shows 10 UIDs that have courses listed next month. 6 do not and are not in the array.

A list of 6 UIDs should be shown after being filtered to show UIDs that are not in the array arrUIDs .

Actual behavior

15 records are shown instead of 6, it looks like the WHERE UID NOT IN (:P1) is only selecting the first record

How to reproduce

Query that pulls out the UIDs with courses next month:

SELECT  DISTINCT CID FROM thedb.courses 
WHERE MONTH(StartDate)  = MONTH(NOW() + INTERVAL 1 MONTH)

Create a flattened array to pull out the UIDs from above

The array pulls out the correct UIDs:
11081,11095,12233,6040,7995,9172,9017,7074,11085,12245

Array is then used to filter the database using a custom and a simple query.

The custom query shows 15 records instead of 6:

SELECT UID, Company, CompEmail, ContactName FROM thedb.ropecompanies 
WHERE UID  NOT IN (:P1) 
AND Trainer = 'Yes' 
AND AccountLevel >0 

Heres the parameter for the above:

The simple query triggers an error:

I’ve tried a number of variations of the above and its been peer tested in a few different ways in this thread (the steps are different below as it uses a list array but the results are the same):

MariaDB NOT IN Statement Not working with Array

Hi All, Wappler: 6.3.3 stable Win 11 MariaDB I’m having issues with a NOT IN statement not behaving as it should. I have 16 example records q0 of them have courses in a table for next month and 6 dont. I need to find the IDs of the 6 that dont ahve the courses listed. I have an array list, I have set a test value ‘FinalUIDList’ below it: [image] The above outputs the following: TotalUIDs 11081,11095,12233,6040,7995,9172,9017,7074,11085,12245 FinalUIDList 11081,11095,12233,6040,7995,91…

[famousmag]

Hey Tom,

Have you tried the result on your browser or just from the query builder notification?

I’m saying that because I have experencied such error Notifications while on query builder but when I finally test on browser it works… Obviously query builder cannot manage some statements
Just saying my friend…

[TMR]

That was well worth a try, here’s what is returned on the front end:

[franse]

Tom, on simple query you need to join and split the values not use the array itself

Check the join and split and test in browser

arrUIDs.join(’,’, ‘ITEM’).split(’,’)
Where “ITEM” is the item of the array to filter

[Teodor]

You cannot use IN and NOT IN like that directly in a custom query.
This has been discussed in a few topics already, for example GET array for use with IN clause of custom query action

[famousmag]

Thanks @Teodor

[TMR]

Thanks for that @Teodor @famousmag and @franse your collective assistance is much appreciated.

For anyone else reading, this was my solution:

SELECT UID, Company, CompEmail, ContactName FROM thedb.companies 
WHERE  NOT FIND_IN_SET(UID, :P1) 
AND Trainer = 'Yes' 
AND AccountLevel >0