hoping someone can shed some light on why this is not working for me. I have a custom query for a notificaiton system of alerts of unread events.
It all works pretty much now, and my sql works in workbench, and, i can get it to work in SC customer query tool, if i hand code the IN operators instead of use the $get variable
what I send from the front to sc is a string that can be A or E or C or A,E ,C or E,C
so i split on the comma as I read on the forum that IN operators have to be an array, and when i do this i get the error.
If I take out the :P2 from the final where clause and have IN ('A','E ') it works. So what am I doing wrong in sending my data and using the dynamic value, I tried not splitting (then i get a differnet sql error) and splitting gives me the array to string error. But, the sql I use works just fine in workbench
I actually think, in custom query it wants the brackets around the :P2 in my example, I just did that and it appears to be working, but, its quite hard to test so testing now lol
this is a simplified version of the sane sql just doing a count
select
count(`Notifications`.`id`) as BadgeTotal
from
`Notifications`
left join `NotificationStatus` as `status` on `status`.`Client_id` = :P1
where
`status`.`IsRead` is null and
`Notifications`.`EnteredBy_id` <> :P1
and (`Notifications`.`TargetClient_id` = :P1 or `Notifications`.`TargetPermission` in (:P2))
take that back, i am getting same results as that link you sent, only taking the first value i think in the IN operator, as in workbench i get a count of 4 and in wappler a count of 1, with same sql, as workbench uses the IN (A,E,C) and dont think wappler is somehow
as @teador posted this on a thread, just to work out how to put that into my code working out what the variable is in that code, i think delivery anyway, will try but goes back to your initial resposne to join (which i guess we have to handcode as join is not in a menu to select dynamically that i can find)
You can't use IN in operator custom queries like this, with dynamic number of parameters, this is only used in regular database query builder. That's already explained in a few topics, for example:
Blast. Not sure how else I can get the data set I need as I need a variable on the left join, I don't suppose there are any good tutorials on here of an alert/notification system like top right on this forum, which if what I have to get built. But notifications can be target to groups of users, where I use my permissions or to individual users
Maybe you can re-think your logic.
You can create an array server list with this.
Using a repeat, for each element add it to the array.
Then you can create a simple query and use the IN operator to see which items are IN the array.
At least, that's the way I've done this.
Saying this because I think the custom query doesn't look too complex
thanks for that, it does make sense, but the problem is that wappler will not support an IN operator with a custom query, you have to use the query builder, and, the query builder will not work as the query builder does not allow a variable on the left join.
I cannot think how to re-structure my data, because I have a table of notifications, i.e a new invoice, a new client account created, a new payment entered, a new client note etc, 99% of the notification events are not a per user case but towards a group of users, so in my notifications table I have a column TargetPermission, then in my sql i use the IN operator to check that permission, but, I then have to check in the NotificationStatus table to see if they the user has an IsRead flag against their user id, and as I do not store their userid in the notifications table as notifications are not target to users specifically) i have no way to join the NotificationStatus table in the sql.
but I do imagine many notification type systems work like this, so either they get the IN or the left join or they have the data structure different, but i cant think how else i can restructure or how else to join the NotificationStatus table
I do keep hitting these little issues in wappler, I envision how to do it, get it to work in workbench so i know my sql can support it, then start to code it up and make it look right then then hit these issues
That is not a Wappler issue. This is how the sql works.
With the custom query you can only create static queries and you can’t have a part that uses a dynamic number of parameters.
For example if you have an array of 3 ids and want to select these 3 ids:
and (`Notifications`.`TargetClient_id` = :P1 or `Notifications`.`TargetPermission` in (:P2))
in your case :P2 is an array with ids as value, this will not work, to make it work you need to know the number of parameters you are using or pass the value.
and (`Notifications`.`TargetClient_id` = :P1 or `Notifications`.`TargetPermission` in (:P2, :P3, :P4))
Or you need to pass the exact values.
So when each value is put in its own parameter it works, but this only is usable when you know the number of parameters to expect.
In the regular query builder it works with {{$GET.permissions.split(',')}} as the query builder builds its query dynamically, it generates the correct sql needed depending on the number of items in the array.
That's why we have the query builder, where you can do this visually, without the need to fight with custom SQL, especially if you're not experienced.
For Postgres, we use string_to_array function to split comma separated string to use in IN condition. Something like: =ANY(string_to_array(:P1,',')
Here, if your :P1 source is an array, just join it. {{$_GET.permissions.join(',')}}.
Something similar for MySQL and MSSQL could work as well.
