You can do it in a single call from php to mysql if you use a stored procedure: Example calls mysql> call category_hier(1); +——–+—————+—————+———————-+——-+ | cat_id | category_name | parent_cat_id | parent_category_name | depth | +——–+—————+—————+———————-+——-+ | 1 | Location | NULL | NULL | 0 | | 3 | USA | 1 | Location … Read more
Adapted from here: SELECT T2.id, T2.name FROM ( SELECT @r AS _id, (SELECT @r := parent_id FROM table1 WHERE id = _id) AS parent_id, @l := @l + 1 AS lvl FROM (SELECT @r := 5, @l := 0) vars, table1 h WHERE @r <> 0) T1 JOIN table1 T2 ON T1._id = T2.id ORDER … Read more
I showed a solution similar to your nodes & edges tables, in my answer to the StackOverflow question: What is the most efficient/elegant way to parse a flat table into a tree? I call this solution “Closure Table”. I did a presentation on different methods of storing and using trees in SQL, Models for Hierarchical … Read more
Store IDs of the objects in a hash table mapping to the specific object. Enumerate through all the objects and find their parent if it exists and update its parent pointer accordingly. In C#: class MyObject { // The actual object public int ParentID { get; set; } public int ID { get; set; } … Read more
Use: SELECT t1.id, t1.parent_id, t1.name, t2.name AS parent_name, t2.id AS parent_id FROM tbl t1 LEFT JOIN tbl t2 ON t2.id = t1.parent_id START WITH t1.id = 1 CONNECT BY PRIOR t1.id = t1.parent_id
Here’s a quick closure table example for SQLite. I’ve not included the statements for inserting items into an existing tree. Instead, I’ve just created the statements manually. You can find the insert and delete statements in the Models for hierarchical data slides. For the sake of my sanity when inserting the IDs for the directories, … Read more
Since you want the elements in the TreeView to have a list of children that consists of both Categories Products, you will want your Category ViewModel to have a collection that consists of both Categories and Products. For example, you could use a CompositeCollection to combine your existing collections: public class Category { public string … Read more
CREATE TABLE #MY_TABLE ( ID INT ,ID_FATHER INT ,COL_X INT ) CREATE TABLE #MY_TABLE_BIS ( MY_ID INT ,X VARCHAR(50) ) CREATE TABLE #OTHER_TABLE ( MY_ID INT ,[ROOT] VARCHAR(50) ) CREATE TABLE #BIG_TABLE ( AN_ID INT ) go DECLARE @MAXDEPTH INT = 10 ;WITH cte_prepare AS ( SELECT ID ,ID_FATHER ,TB.X ,OT.[ROOT] FROM #MY_TABLE T LEFT … Read more
If you’re lucky enough to be using SQLite 3.8.3 or higher then you do have access to recursive and non-recursive CTEs using WITH: Thanks to lunicon for letting us know about this SQLite update. In versions prior to 3.8.3, SQLite didn’t support recursive CTEs (or CTEs at all for that matter) so there was no … Read more
Few thoughts on this: I would try modelling your Neo4j domain model to include the attributes of each node in the graph. By separating your data into two different data stores you might limit some operations that you might want to do. I guess it comes down to what you will be doing with your … Read more