MariaDB NOT IN Statement Not working with Array

Wappler General How To
, ,
#1

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,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?

MySQL/MariaDB NOT IN Statement Not Working With Array
#2

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.

image

image
image1064×599 21.1 KB

image

1 Like
#3

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

#4

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

#5

@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?

#6

Can you show the error? It might be something trivial

#7

image

#8

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

#9

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.

#10

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:
image

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.

#11

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?

#12

I’ve tried this using {{TotalUIDs}} as the condition in a simple query but get this error:

image
image835×41 1.69 KB

image

#13

What about:
{{TotalUIDs.join(',', 'UID').split(',')}}

#14

I tried this one earlier, it gave the following:
image

#15

Can you show the query with the code input?

image
image1064×599 21.1 KB

#16

image
image816×210 7.85 KB

image
image396×582 17.5 KB

#17

Something is wrong
I can see it’s not formatted (It should be green instead of blue) can you show the code?

image
image393×569 17.2 KB

#18

image

#19

sorry, my fault, it was highlighted when i copied it so it was blue instead of green

image

#20

The array schema is correct? UID is the item?
Or how does it look?

image
image858×495 19 KB