Filter data from 1 database in Table depending on user logged in

Account and User Management
1

I have created a tool. The tool is connected to a Retool Database which has a column 'propertycode'. I have 3 propertycodes, example AA/BB/CC and 3 end users. Depending on which end user is logged in I want to load only the rows with AA, BB or CC in the table. To start with I created a query for each propertycode to only load the rows with AA/BB or CC as property code. See below.

query 1;
SELECT *
FROM "breakfastList_all"
WHERE ("propertycode" = 'AA') AND ({{!searchBar.value}} OR fullname ILIKE '%' || {{searchBar.value}} || '%' OR number ILIKE '%' || {{searchBar.value}} || '%')

query 2;
SELECT *
FROM "breakfastList_all"
WHERE ("propertycode" = 'BB') AND ({{!searchBar.value}} OR fullname ILIKE '%' || {{searchBar.value}} || '%' OR number ILIKE '%' || {{searchBar.value}} || '%')

query 3;
SELECT *
FROM "breakfastList_all"
WHERE ("propertycode" = 'CC') AND ({{!searchBar.value}} OR fullname ILIKE '%' || {{searchBar.value}} || '%' OR number ILIKE '%' || {{searchBar.value}} || '%')

To handle user access, my first approach was to create 3 queries, one for each propertycode. And then when user AA is logged in, block query with BB and CC from running, so only the data of query 1 will be shown in the table. However I don't know how to set up that the Table takes data from more then 1 query. Currently my table is connected like this; Table, Data; {{query1.data}}.

Now another approach would be to create 1 query that I connect to table, which specifies already within that query what to load depending on the user logged in. I just don't know how to set it up.....

Anyone that knows this, or are there any other options?

2

You should be able to use one query
SELECT *
FROM "breakfastList_all"
WHERE ("propertycode" = 'CC') AND ({{!searchBar.value}} OR fullname ILIKE '%' || {{ current_user.fullName }} || '%' OR number ILIKE '%' || {{searchBar.value}} || '%')

1 Like
3

Thanks, I needed the right syntax to use the current_user.fullname. It's solved.

SELECT *
FROM "breakfastList_all"
WHERE ("propertycode" =
CASE
WHEN {{ current_user.email }} = 'XXXX@XX.com' THEN 'AA'
WHEN {{ current_user.email }} = 'vXXXX@XX.com' THEN 'BB'
WHEN {{current_user.email }} = 'XXXX@XX.com' THEN 'CC'
ELSE ''
END)
AND (fullguestname ILIKE '%' || {{ search_field.value }} || '%'
OR roomnumber ILIKE '%' || {{ search_field.value }} || '%')

4