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.
-
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