Wappler Version :
Operating System :
Server Model:
Database Type:
Hosting Type:
Expected behavior
What do you think should happen?
It's been a while since I've hand written MySQL queries, but in my experience with MySQL NOT should be right before LIKE in the WHERE clause when using NOT LIKE. Not sure if Wappler has a different way of doing it, but the value being queried should also have wildcards around it when using NOT LIKE (doesn't contain) or LIKE (contains) such as '%value%' or 'value%' etc. It's not throwing any errors so perhaps you can put NOT before the column then LIKE but whatever the case the query is failing and I suspect it has something to do with the wildcards or lack thereof?
Actual behavior
What actually happens?
It's putting NOT before the query column in the condition:
where (`notifications`.`user_id` = ? or `notifications`.`role_id` = ? or `notifications`.`role_id` = ?) and not `notifications`.`notification_read_by` like ?
And I'm not sure where/when Wappler adds or uses the wildcards '%value%' but if it's supposed to be there, they are not.
The query works until I add the final condition of 'AND doesn't contain' then it returns no results and it should return results because that column is NULL right now so it doesn't contain the value I'm testing against. I used "doesn't contain" and it generates this query:
Here is the generated query code:
{
"name": "qNotifications",
"module": "dbconnector",
"action": "select",
"options": {
"connection": "db",
"sql": {
"type": "SELECT",
"columns": [
{
"table": "notifications",
"column": "notification_id"
},
{
"table": "notifications",
"column": "notification_title"
},
{
"table": "notifications",
"column": "notification_icon_class"
},
{
"table": "notifications",
"column": "notification"
},
{
"table": "notifications",
"column": "user_id"
},
{
"table": "notifications",
"column": "role_id"
},
{
"table": "notifications",
"column": "notification_created"
},
{
"table": "notifications",
"column": "notification_updated"
},
{
"table": "notifications",
"column": "notification_read_by"
}
],
"params": [
{
"operator": "equal",
"type": "expression",
"name": ":P1",
"value": "{{qLoggedInUser.user_id}}",
"test": ""
},
{
"operator": "equal",
"type": "expression",
"name": ":P2",
"value": "{{qLoggedInUser.role_id}}",
"test": ""
},
{
"operator": "not_ends_with",
"type": "expression",
"name": ":P3",
"value": "{{','+qLoggedInUser.user_id+','}}",
"test": ""
}
],
"table": {
"name": "notifications"
},
"primary": "notification_id",
"joins": [],
"wheres": {
"condition": "AND",
"rules": [
{
"condition": "OR",
"rules": [
{
"id": "notifications.user_id",
"field": "notifications.user_id",
"type": "double",
"operator": "equal",
"value": "{{qLoggedInUser.user_id}}",
"data": {
"table": "notifications",
"column": "user_id",
"type": "number",
"columnObj": {
"type": "integer",
"primary": false,
"nullable": true,
"name": "user_id"
}
},
"operation": "="
},
{
"id": "notifications.role_id",
"field": "notifications.role_id",
"type": "double",
"operator": "equal",
"value": "{{qLoggedInUser.role_id}}",
"data": {
"table": "notifications",
"column": "role_id",
"type": "number",
"columnObj": {
"type": "integer",
"primary": false,
"nullable": true,
"name": "role_id"
}
},
"operation": "="
},
{
"id": "notifications.role_id",
"field": "notifications.role_id",
"type": "double",
"operator": "equal",
"value": 4,
"data": {
"table": "notifications",
"column": "role_id",
"type": "number",
"columnObj": {
"type": "integer",
"primary": false,
"nullable": true,
"name": "role_id"
}
},
"operation": "="
}
],
"conditional": null
},
{
"id": "notifications.notification_read_by",
"field": "notifications.notification_read_by",
"type": "string",
"operator": "not_ends_with",
"value": "{{','+qLoggedInUser.user_id+','}}",
"data": {
"table": "notifications",
"column": "notification_read_by",
"type": "text",
"columnObj": {
"type": "text",
"maxLength": 65535,
"primary": false,
"nullable": true,
"name": "notification_read_by"
}
},
"operation": "NOT LIKE"
}
],
"conditional": null,
"valid": true
},
"orders": [
{
"table": "notifications",
"column": "notification_created",
"direction": "DESC",
"recid": 1
}
],
"query": "select `notification_id`, `notification_title`, `notification_icon_class`, `notification`, `user_id`, `role_id`, `notification_created`, `notification_updated`, `notification_read_by` from `notifications` where (`notifications`.`user_id` = ? or `notifications`.`role_id` = ? or `notifications`.`role_id` = ?) and not `notifications`.`notification_read_by` like ? order by `notification_created` DESC"
}
},
"output": true,
"meta": [
{
"type": "number",
"name": "notification_id"
},
{
"type": "text",
"name": "notification_title"
},
{
"type": "text",
"name": "notification_icon_class"
},
{
"type": "text",
"name": "notification"
},
{
"type": "number",
"name": "user_id"
},
{
"type": "number",
"name": "role_id"
},
{
"type": "datetime",
"name": "notification_created"
},
{
"type": "datetime",
"name": "notification_updated"
},
{
"type": "text",
"name": "notification_read_by"
}
],
"outputType": "array",
"collapsed": true
},
Here is a dump of the notifications table if needed for testing:
notifications.zip (1.5 KB)
How to reproduce
I tested it and was able to reproduce it by creating another query with 3 OR conditions and an AND doesn't contain condition. It put the NOT before the query column and I don't see any wildcards.
Unless I'm missing something simple, this query should return results.
Thanks in advance,
Twitch
