Error when using IN operator

psweb · November 23, 2022, 8:26am

Wappler 5.2.4, M1 Mac.

When doing a standard Database Query and using something like this

I get this error

code: "ER_PARSE_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.

Teodor · November 23, 2022, 8:45am

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?

patrick · November 23, 2022, 8:58am

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

psweb · November 23, 2022, 9:02am

I have this as my server action
2022-11-23_10-59-42

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.

Teodor · November 23, 2022, 9:03am

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.

psweb · November 23, 2022, 9:13am

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

psweb · November 23, 2022, 9:16am

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

patrick · November 23, 2022, 9:32am

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.

psweb · November 23, 2022, 9:36am

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.

patrick · November 23, 2022, 9:43am

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.

psweb · November 23, 2022, 9:49am

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.

psweb · November 23, 2022, 9:55am

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)

Notum · November 24, 2022, 10:08am

offtopic
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.

psweb · November 24, 2022, 11:23am

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.