Error when using IN operator

Wappler 5.2.4, M1 Mac.

When doing a standard Database Query and using something like this

I get this error

message: "select * from `staff_employee_mapping` where `staff_employee_mapping`.`sem_group_ids` in '16' - ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''16'' at line 1"
stack: "Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''16'' at line 1\n    at Query.Sequence._packetToError (/opt/node_app/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)\n    at Query.ErrorPacket (/opt/node_app/node_modules/mysql/lib/protocol/sequences/Query.js:79:18)\n    at Protocol._parsePacket (/opt/node_app/node_modules/mysql/lib/protocol/Protocol.js:291:23)\n    at Parser._parsePacket (/opt/node_app/node_modules/mysql/lib/protocol/Parser.js:433:10)\n    at Parser.write (/opt/node_app/node_modules/mysql/lib/protocol/Parser.js:43:10)\n    at Protocol.write (/opt/node_app/node_modules/mysql/lib/protocol/Protocol.js:38:16)\n    at Socket.<anonymous> (/opt/node_app/node_modules/mysql/lib/Connection.js:88:28)\n    at Socket.<anonymous> (/opt/node_app/node_modules/mysql/lib/Connection.js:526:10)\n    at Socket.emit (node:events:513:28)\n    at addChunk (node:internal/streams/readable:315:12)\n    --------------------\n    at Protocol._enqueue (/opt/node_app/node_modules/mysql/lib/protocol/Protocol.js:144:48)\n    at Connection.query (/opt/node_app/node_modules/mysql/lib/Connection.js:198:25)\n    at /opt/node_app/node_modules/knex/lib/dialects/mysql/index.js:132:18\n    at new Promise (<anonymous>)\n    at Client_MySQL._query (/opt/node_app/node_modules/knex/lib/dialects/mysql/index.js:126:12)\n    at executeQuery (/opt/node_app/node_modules/knex/lib/execution/internal/query-executioner.js:37:17)\n    at Client_MySQL.query (/opt/node_app/node_modules/knex/lib/client.js:144:12)\n    at Runner.query (/opt/node_app/node_modules/knex/lib/execution/runner.js:130:36)\n    at ensureConnectionCallback (/opt/node_app/node_modules/knex/lib/execution/internal/ensure-connection-callback.js:13:17)\n    at Runner.ensureConnection (/opt/node_app/node_modules/knex/lib/execution/runner.js:272:20)"
status: "500"

And the query does indeed look wrong, I think it should be

"select * from `staff_employee_mapping` where `staff_employee_mapping`.`sem_group_ids` in (16) 

When testing this in Navicat, I can confirm it does not work, until I add the () around the value.

If I click the Custom button of the bottom left corner, and make no modifications at all, it works perfectly.

What does your $_GET.group_filter return as value? Can you please output it in a setvalue step and paste the exact value you see?

The value must be an array, if you want to compare with a single value you should use equal instead of in.

1 Like

I have this as my server action

If I add the GET into a setValue step it outputs as

My database looks like this

What I am trying to do is some filtering.

Please check Patrick’s reply:

your value needs to be an array for the IN filtering. So if your GET value is a comma separated list of values, then you need to split it like {{$_GET.group_filter.split(',')}} which will turn "46,50" to ["46","50"] as the query expects it.

1 Like

Ahh I see, thank you for showing it so nicely, that makes sense.

Thanks @Teodor and @patrick that got it working now without a custom query.

1 Like

It is not smart to have the group ids as a comma separated list in the database, doing a SELECT * FROM staff_employee_mapping WHERE sem_group_ids IN (1, 5) will return the records where sem_group_ids is 1 or 5, but not the one that has 1,3 or 14,17,13,5.

1 Like

Its not generally something i do, however in this case I will only ever give it a single value that the user selects from a drop down list, so it will never be IN (1, 5) but just IN (5) as an example.
I do however agree, i do not generally add comma separated values to databases where avoidable.

But IN (5) will not match the record with 14,17,13,5. And using IN with a single value is the same as using equal = 5. The IN is for matching multiple values and not for matching a part of a string.

1 Like

Ahhh, I see what you are saying, just testing it a bit in Navicat, and yes, you are right, it does not work like i was expecting, I will have to give this a little rethink, thank you Patrick, that could have been a bit of a mess.

Would it be terrible to get around it using a custom query with something like this
SELECT * FROM staff_employee_mapping WHERE FIND_IN_SET(5,sem_group_ids)

Oh man, oh man.
Handling group membership with comma separated values, rather then using conjunction tables… Ehhhh… I hope you know what are you doing.

1 Like

Yeah, it does not handle group membership at all and is working well for what it needs to do, its not really handling anything more then mapping, the user, groups, permissions, position, types, tables are all multi reference tables and controlled by primary/foreign keys. This table is really more for ease of display and nothing more, so really of little importance, and the csv values were just easier to store like this as they come from a bunch of tagify elements.