MariaDB NOT IN Statement Not working with Array

TMR · March 24, 2024, 4:11pm

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:

The above outputs the following:

TotalUIDs

11081,11095,12233,6040,7995,9172,9017,7074,11085,12245

FinalUIDList

11081,11095,12233,6040,7995,9172,9017,7074,11085,12245

The above array pulls out a list of 10 UIDs that I need to use to filter a MySQL query:

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

When I hard code the array (10 records) into the query it works as it should, it shows the 6 records that are not in the array above:

SELECT UID,Company,CompEmail,ContactName FROM mydb.ropecomp 
WHERE UID  NOT IN (11081,11095,12233,6040,7995,9172,9017,7074,11085,12245) 
AND Trainer = 'Yes' 
AND AccountLevel >0

I’ve tried all variations on the above but I still cant get the NOT IN statement to return the correct records, can anyone out there lend a hand please?

franse · March 24, 2024, 4:59pm

Something says to me I don’t fully understand the issue, but
Have you tried: TotalUIDS.join(',', 'UID').split(',') ?

I made a test and got it working:
Two tables:
One: 1,2,3
Two: 1,2,3,4

The query returns the items that are not in the array.

famousmag · March 24, 2024, 5:04pm

Agree with @franse, Works fine for me also.
Buy we use simple query not custom…
I tried custom query and couldn’t make it

Apple · March 24, 2024, 5:11pm

I think you’ll need to get rid of :P1 and literally inject the expression there {{TotalUIDs}}

Be aware, for security purposes, you need to guarantee list will only contain numbers, else becomes a SQL Injection security vulnerability

FinalUIDList should have square brackets, did you miss those? Not that it matters for the current solution, it’s just a potentially important detail

TMR · March 24, 2024, 6:35pm

@Apple @franse @famousmag appreciate the feedback but still no solution, I tried everything above and I still cant get correct records.

I forgot to mention, this is a Scheduled task so I tried just creating a ‘normal’ API and still get the same results.

The SQL is fine, its the way Wappler is handling the :P1 Parameter that’s the issue here I think, I’m kind of suspecting its a bug at this moment in time. This is a fairly simple statement so I’m surprised its causing any issues.

Injecting the expression {{TotalUIDs}} just caused an error.

Any more ideas?

Apple · March 24, 2024, 6:52pm

Can you show the error? It might be something trivial

TMR · March 24, 2024, 6:54pm

Apple · March 24, 2024, 6:59pm

Interesting, seems it’s no longer possible to inject expressions into the SQL query

New approach, adapt to yours:

SELECT product_id, product_name, category
FROM products
WHERE category IN (SELECT TRIM(value) FROM STRING_SPLIT(:P1, ','));

Source

Hyperbytes · March 24, 2024, 8:41pm

Can you explain the data structure?
Is the data in two tables or are we back on comma separated list territory.
If tables this should be possible via a left join without the need for arrays.

TMR · March 24, 2024, 9:20pm

Its a simple structure, from only one table
UID,Company,CompEmail,ContactName

The SQL is sound, its taken from a working site (ASP Classic) thats been working for years.

The issue is with the way Wappler is formatting the data, a Wappler array is created from the MySQL query:

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

The above statement pulls out 10 records from a possible 16, those records are used to create the Wappler created array listed above, here it is again:

The array is then used to select the remaining (6) records that are not in the array above:

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

the problem is here: (:P1)

I’m going to report this as a bug as there’s clearly an issue with the formatting of the Wappler created array.

franse · March 24, 2024, 9:35pm

I can’t use NOT IN with a custom query neither, maybe it’s a bug.
But why you can’t do this with a simple query?
Sorry if it’s an obvious question and I’m not seeing viable that workaround

You have 16 records on a table, 10 inside an array and you need to retrieve the other 6?