When I want to use an IN() with an array or a comma delimited list of values, for instance using a multi Select component, I would structure my WHERE clause like this:
status_id in (SELECT value FROM STRING_SPLIT({{bgStatus.value}}, ','))
But this does not work with AlaSQL, STRING_SPLIT is not a recognized function.
I have flailed around trying other things and Googled a lot, I won't bore you with what I tried as they all failed, I cannot fund a way in AlaSQL to get a result set from a delimited string or array.
What does bgStatus.value look like initially?
I’ve run into a similar issue recently and solved by rewriting the original query. So, I may have a solution.
Thanks for taking a stab. It is a comma delimited string like: "1,2,5,6"
I can of course make it an array easily enough. This is my actually query and it is the first element of the WHERE clause that is the problem:
select *
from {{formatDataAsArray(qryProjectsSelect.data)}}
where project_status_id in (select value from STRING_SPLIT({{trProjectFilterStatus.value}}, ','))
AND ({{selFilterProjectType.value || null}} IS NULL OR project_type_id={{selFilterProjectType.value}})
AND ({{selFilterClient.value || null}} IS NULL OR client_id={{selFilterClient.value}})
AND (1 = {{swFilterNeedsAssign.value === true ? 0: 1}} or assignments_filled = false)
AND (1 = {{swFilterNeedsApproval.value === true ? 0: 1}} or swFilterNeedsApproval = false)
AND ({{dtFilterStartBefore.value || ""}} ='' OR DATE(date_time_end)<=DATE({{dtFilterStartBefore.value}}))
AND ({{dtFilterStartAfter.value || ""}} ='' OR DATE(date_time_start)>=DATE({{dtFilterStartAfter.value}}))
I have a segmented button that is acting as a toggle between Incomplete and Complete is used as a quick filter on project_status_id. The user can alternatively select a specific status using selFilterStatus. The transformer has the logic to determine which statuses should be selected:
// trProjectFilterStatus
let selStatus = {{selFilterStatus.value}}
let togStatus = {{segFilterStatus.value}}
if (selStatus) {
return selStatus.toString()
}
if (togStatus) {
return "7"
}
return "1,2,5,6"
I have an idea for a brute force method, but it I'd rather not go there yet. I can also just hit the server instead, but I prefer the JSON SQL method for performance reasons.
Can you try status_id in @({{bgStatus.value}}) and let us know if that works? AlaSQL has some specific syntax for approximating JavaScript in various cases that may be worth checking out.