Search Posts

Category: hierarchical-data

Get Category Level [duplicate]

how to – Get Category Level [duplicate] This question already has an answer here: Getting all parent rows in one SQL query 5 answers In MySql I have category table with fields: cat_id (int) cat_parent_id (int) cat_name (string) How to create query that return list off all categoryes with coresponded level number cat parent_id for root nodes is 0 sample SQL Fiddle with sample data : http://sqlfiddle.com/#!9/ba439ae/3 I have MySql version : 5.7.19 and Fiddle […]

Generating Materialized Path with Recursive Common Table Expression in MariaDB

how to – Generating Materialized Path with Recursive Common Table Expression in MariaDB I have been working through this excellent article on how to use Recursive Common Table Expressions along with a Tally table to convert an adjacency list to a nested set. The one hitch I have is that I need to convert the code from MSSQL to MySQL. MSSQL Version: –=========================================================================== — 1. Read ALL the nodes in a given level as indicated […]

Determine if a record is a descendant of another record using recursive CTE

how to – Determine if a record is a descendant of another record using recursive CTE The following table stores topics in an instruction manual. Each topic has a parent topic except for record id of 1 which is the root. The application allows all topics except for the root topic to have the parent changed, and I will need to prevent an ancestor from being assigned as a descendant of itself. For instance, record […]

How to store efficiently hierarchical data in PostgreSQL (LFT/RGT vs PARENT_ID)

We have a Java application and we want to create an abstract HierarchicalId class to use as an extend of any entity that is part of a tree. Our experience in MySQL showed us that it is much more efficient for READ operation to store the left and right position. See http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ for more details We will read 95% of the time, but the 5% remaining will still require CREATE/UPDATE/DELETE, we also need to be […]