recursive queries #109
Replies: 2 comments 5 replies
-
|
Do you know what the depth will be? |
Beta Was this translation helpful? Give feedback.
-
|
actually no, I use a calculated value to get it. like so: const queries = useCreateQueries(store, (store) => {
return createQueries(store).setQueryDefinition(
'listPages',
'pages',
({select}) => {
select('id');
select('name');
select('parentId');
select('index');
// the calculated query to get the depth based on parentId that match with an id
select(
(getTableCell) =>
calculateDepth(Object.values(store.getTable('pages')), getTableCell('id')),
).as('depth');
},
);
});The function to calculate the depth increments an iterator "currentDepth" when it finds a parentId and remembers about the visited nodes in order not to get into an infinite loop Here is the function calculateDepthconst calculateDepth = (pages, id: string, currentDepth: number = 0, visitedNodes: Set<string> = new Set()): number => {
const page = pages.find((item) => item.id === id);
if (!page || visitedNodes.has(id)) {
return currentDepth;
}
visitedNodes.add(id);
const parentId = page.parentId;
if (parentId) {
return calculateDepth(pages, parentId, currentDepth + 1, visitedNodes);
}
return currentDepth;
};and today my recursive query is completed by a good old js function that sorts my tree: first by children, then by index const listPagesQueryResult = useResultTable('listPages');
const sortedPages = useMemo(() => {
const recursiveSort = (pages, parentId = '') => {
return pages
.filter(page => page.parentId === parentId)
.sort((page1, page2) => page1.index.localeCompare(page2.index))
.flatMap(child => [child, ...recursiveSort(pages, child.id)]);
};
return recursiveSort(Object.values(listPagesQueryResult));
}, [listPagesQueryResult]);output |
Beta Was this translation helpful? Give feedback.
-
|
In the Intro to Relationships docs it says:
But for self-referential tables the typical use-case is usually for a parent/child relationship so I was hoping to see an api like |
Beta Was this translation helpful? Give feedback.
-
|
It turns out that import { createRelationships, createStore } from "tinybase";
const store = createStore().setTable("nodes", {
d1: { name: "data", pId: null },
f1: { name: "file1", pId: "d1" },
f2: { name: "file2", pId: "d1" },
f3: { name: "file3", pId: null },
});
const rel = createRelationships(store);
rel.setRelationshipDefinition("nodeParents", "nodes", "nodes", "pId");
console.log("Parent of f1", rel.getRemoteRowId("nodeParents", "f1"));
// Prints 'd1'
console.log("Children of d1", rel.getLocalRowIds("nodeParents", "d1"));
// Prints ['f1', 'f2']
console.log("Parent of d1", rel.getRemoteRowId("nodeParents", "d1"));
// Prints undefined |
Beta Was this translation helpful? Give feedback.
-
|
I was wondering if TinyBase would maintain linked list when I do this and whether that would be useful to me at all. It looks like if you do a self-referential table like this, you can still get some use out of import { createRelationships, createStore } from "tinybase";
const store = createStore().setTable("nodes", {
d1: { name: "data", pId: null },
d2: { name: "dir2", pId: "d1" },
f1: { name: "file1", pId: "d1" },
f2: { name: "file2", pId: null },
f3: { name: "file3", pId: "d2" },
});
const rel = createRelationships(store);
rel.setRelationshipDefinition("nodeParents", "nodes", "nodes", "pId");
console.log("Parent of d2", rel.getRemoteRowId("nodeParents", "d2"));
// Prints 'd1'
console.log("Children of d1", rel.getLocalRowIds("nodeParents", "d1"));
// Prints ['d2', 'f1']
console.log("Parent of d1", rel.getRemoteRowId("nodeParents", "d1"));
// Prints undefined
console.log("Linked to d1", rel.getLinkedRowIds("nodeParents", "d1"));
// Prints ['d1'] (depth = 1)
console.log("Linked to f1", rel.getLinkedRowIds("nodeParents", "f1"));
// Prints ['f1', 'd1'] (depth = 2)
console.log("Linked to f3", rel.getLinkedRowIds("nodeParents", "f3"));
// Prints ['f3', 'd2', 'd1'] (depth = 3)
store.setRow("nodes", "f4", {
ctime: 0,
name: "file4",
pId: "d2",
});
// Test calculating depth in a query...
function calculateDepth(_get, rowId) {
return rel.getLinkedRowIds("nodeParents", rowId).length - 1;
}
const q = createQueries(store);
q.setQueryDefinition("q1", "nodes", ({select, join, where}) => {
select("name");
select(calculateDepth).as("depth");
select(get => get("p", "name")).as("parent");
join("nodes", "pId").as("p");
// where("pId", "d2");
});
console.log("q1", q.getResultTable("q1"));
// Prints:
q1 {
d1: { name: 'data', depth: 0 },
d2: { name: 'dir2', depth: 1, parent: 'data' },
f1: { name: 'file1', depth: 1, parent: 'data' },
f2: { name: 'file2', depth: 0 },
f3: { name: 'file3', depth: 2, parent: 'dir2' },
f4: { name: 'file4', depth: 2, parent: 'dir2' }
} |
Beta Was this translation helpful? Give feedback.
-
|
One thing I'm not sure of is if mixing use of the |
Beta Was this translation helpful? Give feedback.
-
|
@waynesbrain I found your comments about queries and indexes here and in another issue very helpful, thank you! |
Beta Was this translation helpful? Give feedback.
Uh oh!
There was an error while loading. Please reload this page.
Uh oh!
There was an error while loading. Please reload this page.
-
I am storing a tree in tinybase.
Example (click me)
:basically, to know if a node is attached to a parent node, I have a parentId column. I also put a foreign key on.
now I want to do like a recursive query to build my tree in the form
I wonder how a recursive query would look like in tinybase. Here is an example in sql (see example 5) https://learnsql.com/blog/query-parent-child-tree/
Beta Was this translation helpful? Give feedback.
All reactions