Hi.
I have a list view web element that displays users from users_db table
Users have assets to them that are fetched from asset_db
It has a foreign key custodian_user_id to users_db.id
I'm trying to display a list of users where every entry would have a nested list_view of assets that this user is in charge of. So I have to limit the return of the assets_db list view element to contain only assets that current user is in charge of. In other words assets_db.custodian_user_id == {{item.id}} from the users_db list view element.
And passing {{item.id}} from the top list to the nested one proved to be a bit more difficult than I anticipated.
Would appreciate any advice on best practice to work this out.
Hi,
In your first query where you fetch users, you can have a after success js script that loops through each user and fetches it's assets and adds them to the item.
After that, in your nested list view you can retrieve them with item.assets
1 Like
On the SQL portion, you're going to want to join them rather than trying to run the query for each ID from your users table.
select
*
from users_db
-- inner join to exclude users without assets, left join to include them
inner join asset_db on users_db.id = asset_db.custodian_user_id
order by users_db.id
This will return a row for every combination of user and asset.
Now you can transform these rows into an array of nested objects for your listviews.
// for simplicity lets take our results arrays and turn them into objects
const dataObjects = formatDataAsArray(data);
return dataObjects.reduce((acc, obj) => {
//build the nested object first, using the columns from the assets table. If you include users with no assets you'll need to build logic to skip this stuff.
let arrayData = { key: assetData1, otherKey: assetData2 };
//since users will appear multiple times when they have more than one asset we check if we've already built the object.
if (acc[obj.id].id) {
//if the object exists, just push the new nested data into the existing array
acc[obj.id].nestedData.push({
arrayData,
});
} else {
//if the object doesn't exist, build it from the user table columns and assign it to the accumulator
acc[obj.id] = {
topLevelKey: userData1,
otherTopLevelKey: userData2,
nestedData: [arrayData],
};
}
return acc;
}, {});
Now, with this sample data:
The listview setup should look like this:
Outer -
Inner -
2 Likes
Hi @kschirrmacher,
Following through above for a similar implementation and got lost real early...
My tables are a parents table and children table and query
SELECT
p.id, p.name as PName, p.email, c.name
FROM
"parentGuardianA" p
right join "childA" c on p.id = c."parentID"
order by p.id
returns desired data as below (highlighted some repeats)
Didn't understand the transform portion and whether you running that in the sql query or a separate js query (new to js so the breakdown not understood fully).
After the 'transformation' how you link the data to the parent listview and also the child listview.
1 Like
Hello!
The duplicates are expected in the SQL query. What you're doing with the join is creating a row for every combination of the two tables that meet your join conditions. If a parent has three kids then you'll expect three rows, one for each combination of parent-kid relations.
I'd run the transform in the SQL query itself. In the screenshot you'll see the box where it goes. Select 'enable' and a text area will appear to run the JS. This happens automatically so no need to trigger another query or anything.
In this case I think you'd want the code to look something like:
const dataObjects = formatDataAsArray(data);
return dataObjects.reduce((acc, obj) => {
//build the nested object first, using the columns from the assets table. If you include users with no assets you'll need to build logic to skip this stuff.
let arrayData = obj.name
//since users will appear multiple times when they have more than one asset we check if we've already built the object.
if (acc[obj.id].id) {
//if the object exists, just push the new nested data into the existing array
acc[obj.id].child_names.push({
arrayData,
});
} else {
//if the object doesn't exist, build it from the user table columns and assign it to the accumulator
acc[obj.id] = {
pname: obj.pname,
email: obj.email,
child_names: [arrayData],
};
}
return acc;
}, {});
Now you should be able to set your outer listview source to the query result and the inner listview source to {{item.child_names}}
1 Like
Updated code as below
const dataObjects = formatDataAsArray(data);
return dataObjects.reduce((acc, obj) => {
if (acc[obj.id]) {
//Add child element
acc[obj.id].child_names.push(
obj.name
);
} else {
//if the object doesn't exist (parent)
acc[obj.id] = {
name: obj.pname,
email: obj.email,
child_names: [obj.name]
};
}
return acc;
}, {});
Data now good but struggle to display the inner listview with {{item.child_names}}. it is only displaying the number of children the number of times that parent ahs children i.e. if two children, showing the line twice for child one....
It looks like you're close. Using a snippet of your data here's how I set the outer and inner listviews.
Outer:
Inner:
1 Like
