I'm trying to build a dynamic navigation menu that I can save as a shared resource. This navigation menu will be created from a SQL query against the internal retool database using the logged in users email address and then going through the various tables to get the pages this user is allowed to view and interact with. That part I have working with the following query
SELECT p.name as pageName, f.name as folderName
FROM users u
JOIN user_groups ug ON u.id = ug."userId"
JOIN group_pages gp ON ug."groupId" = gp.id
JOIN pages p ON gp."pageId" = p.id
JOIN folders f on p."folderId" = f.id
WHERE u.email = {{current_user.email}}
AND (ug."groupId" = 1 OR gp.id != 1)
AND f.name NOT IN ('deprecated', 'archive')
UNION
SELECT p.name as pageName, f.name as folderName
FROM pages p
JOIN folders f on p."folderId" = f.id
WHERE EXISTS (
SELECT 1
FROM users u
JOIN user_groups ug ON u.id = ug."userId"
WHERE u.email = {{current_user.email}} AND ug."groupId" = 1)
AND f.name NOT IN ('deprecated', 'archive')
;
What I can't really find information on is how to dynamically build a navigation menu from javascript. Ideally this script would generate a nested menu with the folderName being the top level menu item, and then listing the child pages under that parent heading.
For example let's say I have a query response that has folder 1 with 5 pages, and folder 2 with 5 pages. The navigation menu then should have 2 top level items, folder 1 and folder 2, each with it's respective children listed within it.
I have javascript doing this as well and saving it to a state object where each folder is the parent key, and within it are the child pages.
function transformToMenuData(dbResults) {
const menuData = {};
for (let key in dbResults.pagename) {
if (dbResults.pagename.hasOwnProperty(key) && dbResults.foldername.hasOwnProperty(key)) {
const folder = dbResults.foldername[key];
const page = dbResults.pagename[key];
if (!menuData[folder]) {
menuData[folder] = [];
}
menuData[folder].push(page);
}
}
return menuData;
}
state1.setValue(transformToMenuData(getUsersApps.data));
What I'm stuck on is getting from the temporary state object into a working navigation menu component.
The navigation component should be able to handle this use case, it has the concept of parent and child items and you can use your query data as the mapped data for it.
Yes, but how do you make those actually link to another app? It needs to be the other apps uuid from the pages table, but where do I put that value in the mapped menu items?
Updated js that works just like your example above, but I'm still missing that one piece to make these actually link somewhere.
function transformToMenuData(dbResults) {
const menuData = [];
for (let key in dbResults.pagename) {
if (dbResults.pagename.hasOwnProperty(key) &&
dbResults.foldername.hasOwnProperty(key) &&
dbResults.apptarget.hasOwnProperty(key)) {
const folder = dbResults.foldername[key];
const page = dbResults.pagename[key];
const appTarget = dbResults.apptarget[key];
menuData.push({ label: page, value: appTarget, parent: folder });
}
}
return menuData;
}
state1.setValue(transformToMenuData(getUsersApps.data));
return transformToMenuData(getUsersApps.data);
And the sql
SELECT f.name as foldername, p.name as pagename, p.uuid as apptarget
FROM users u
JOIN user_groups ug ON u.id = ug."userId"
JOIN group_pages gp ON ug."groupId" = gp.id
JOIN pages p ON gp."pageId" = p.id
JOIN folders f on p."folderId" = f.id
WHERE u.email = {{current_user.email}}
AND (ug."groupId" = 1 OR gp.id != 1)
AND f.name NOT IN ('deprecated', 'archive')
UNION
SELECT f.name as folderName, p.name as pageName, p.uuid as appTarget
FROM pages p
JOIN folders f on p."folderId" = f.id
WHERE EXISTS (
SELECT 1
FROM users u
JOIN user_groups ug ON u.id = ug."userId"
WHERE u.email = {{current_user.email}} AND ug."groupId" = 1)
AND f.name NOT IN ('deprecated', 'archive')
ORDER BY pageName
;
The click event handler of the navigation component has access to the {{item}} in your data, so if each entry in your data has the UUID of the retool app you want to open, you can trigger it to open with a script that uses the utils.openApp() function
1 Like
Got it all working.
SQL
SELECT sub.folderName, sub.pageName, sub.appTarget
FROM (
SELECT
CASE
WHEN f.name = 'root' THEN 'Home'
ELSE f.name
END as folderName,
p.name as pageName,
p.uuid as appTarget,
CASE
WHEN f.name = 'root' THEN 1
WHEN f.name = 'Benchling Tools' THEN 2
WHEN f.name = 'Data Platform' THEN 3
WHEN f.name = 'Mass Spec' THEN 4
WHEN f.name = 'Metrics' THEN 5
WHEN f.name = 'Utilities' THEN 6
ELSE 7
END as orderPriority
FROM users u
JOIN user_groups ug ON u.id = ug."userId"
JOIN group_pages gp ON ug."groupId" = gp.id
JOIN pages p ON gp."pageId" = p.id
JOIN folders f on p."folderId" = f.id
WHERE u.email = {{current_user.email}}
AND (ug."groupId" = 1 OR gp.id != 1)
AND f.name NOT IN ('deprecated', 'archive')
UNION
SELECT
CASE
WHEN f.name = 'root' THEN 'Home'
ELSE f.name
END as folderName,
p.name as pageName,
p.uuid as appTarget,
CASE
WHEN f.name = 'root' THEN 1
WHEN f.name = 'Benchling Tools' THEN 2
WHEN f.name = 'Data Platform' THEN 3
WHEN f.name = 'Mass Spec' THEN 4
WHEN f.name = 'Metrics' THEN 5
WHEN f.name = 'Utilities' THEN 6
ELSE 7
END as orderPriority
FROM pages p
JOIN folders f on p."folderId" = f.id
WHERE EXISTS (
SELECT 1
FROM users u
JOIN user_groups ug ON u.id = ug."userId"
WHERE u.email = {{current_user.email}} AND ug."groupId" = 1)
AND f.name NOT IN ('deprecated', 'archive')
) as sub
ORDER BY sub.orderPriority, sub.pageName;
Javascript query to transform the SQL results
function transformToMenuData(dbResults) {
const menuData = [];
for (let key in dbResults.pagename) {
if (dbResults.pagename.hasOwnProperty(key) &&
dbResults.foldername.hasOwnProperty(key) &&
dbResults.apptarget.hasOwnProperty(key)) {
const folder = dbResults.foldername[key];
const page = dbResults.pagename[key];
const appTarget = dbResults.apptarget[key];
menuData.push({ label: page, value: appTarget, parent: folder });
}
}
return menuData;
}
state1.setValue(transformToMenuData(getUsersApps.data));
And one more silly JS script to handle the menu click
utils.openApp(state1.value);
There's probably a more elegant way to do this, but this works and that's all I set out to do.
Thanks @dcartlidge for pointing me in the right direction!
