Hey I want to filter a table by the labelname or labelvalue that I select in the filter (could be just one or multiple). I tried getting gemini and ChatGPT to alter the sql statement for me (i have little coding knowledge) but the all didnt work. The Name for the component is {{ multiselect.1selectedlabels }}.
These were the solutions by Gemini & ChatGPT:
GEMINI:
WITH selected_indications AS (
SELECT unnest(:{{ multiselect1.selectedLabels }}) AS indication
)
SELECT *
FROM "Angebote"
JOIN "HanseMerkur" ON "Angebote".id = "HanseMerkur".angebote_id
WHERE
-- Check if multiselect is empty
CASE WHEN :{{ multiselect1.selectedLabels }} IS NULL OR array_length(:{{ multiselect1.selectedLabels }}) = 0 THEN
-- Execute original query if empty
TRUE
ELSE
-- Filter by indication if not empty
"HanseMerkur".indication IN (SELECT indication FROM selected_indications)
END
ORDER BY "HanseMerkur".id ASC;
ChatGPT:
DECLARE @sqlQuery NVARCHAR(MAX)
IF NOT EXISTS (SELECT * FROM {{ multiselect1.selectedLabels }})
BEGIN
-- Wenn das Array leer ist, führe die normale SQL-Anfrage aus
SET @sqlQuery = '
SELECT *
FROM "Angebote"
JOIN "HanseMerkur" ON "Angebote".id = "HanseMerkur".angebote_id
ORDER BY "HanseMerkur".id ASC;'
END
ELSE
BEGIN
-- Wenn das Array nicht leer ist, filtere nach den ausgewählten Labels
SET @sqlQuery = '
SELECT *
FROM "Angebote"
JOIN "HanseMerkur" ON "Angebote".id = "HanseMerkur".angebote_id
WHERE "HanseMerkur".indication IN (''' + STRING_AGG(QUOTENAME(value), ''',''') + ''')
ORDER BY "HanseMerkur".id ASC;'
END
-- Ausführen der dynamischen SQL-Anfrage
EXEC sp_executesql @sqlQuery
Since this is only one of the filters I want to add I'm unsure if this is the most effecient way to implement it but I currently have no other idea apart from maybe having a seperate filter query instead of it being in the regular table query.
As always any help is greatly appreciated <3
Hello! I'm not sure I understand the issue but you can probably use the table filters from retool instead of trying to modify the query.
Add an event handler to the text input and make that set filters in the table.
Hey @GuilhermeSilva I need to have filters in german so I cant use the filter componenent available. But if i can change the filters in the background that would work. I couldnt find a way to do that though
You should be able to change them programmatically with the table's setFilter() method:
[via table docs]
This way, wherever that Javascript code lives, you can access the Select component's value in German and use that with the method. (I haven't tried this, but I believe it should work.)
Note that this would be filtering post SQL query, so depending on your performance needs it might not be appropriate. But I take "in the background" to mean that it's okay to perform locally/in the client.
This way you'll also have control over the timing of the filtering (when to start, or any debouncing or throttling of user input, etc).
Thank you @dguzzo! Where would I add the code to change the filters? I couldnt find a place to add JS in the Table component.
Hey @KungFooLemmi, you can set the events anywhere in the app, for example on the text inputs, you can set a event handler where the event is "Submit" and apply filters to the table, by selecting "Control Component", selecting your table and then selecting "Set Filter" or "Set Filter Stack".
1 Like
I tried it with the transformer but now it doesnt show anything. Is the transformer the right way to do this?
For multiple filters you should use setFilterStack() with multiple filters, like so:
When I mentioned setting the filters I was referring to something like this (I tried it locally and I believe it's the behavior you are looking for):
1 Like
@GuilhermeSilva I tried it like this and it didnt work. Am i missing something?
Thanks so much for your continous help! 
In this case I think the issue is that you are using "multiselect1.selectedLabels", that means that "item.value" at the end will not exist.
You can either:
Use "multiselect1.selectedLabels" and then at the end just use "item".
Use "multiselect1.selectedItems" and then at the end use "item.indication"
Let me know how it goes!
2 Likes
It worked!! Thank you so much and have a great day!! 
1 Like
