Mapping a Tree with GORM and Grails

MAPPING
A TREE
WITH
GRAILS
objectcomputing.com/grails

View Slide

http://sergiodelamo.es
Sergio del Amo
@sdelamo

View Slide

http://groovycalamari.com

View Slide

http://guides.grails.org

View Slide


View Slide

PROBLEM
STORE & QUERY
HIERARCHICAL DATA

View Slide

TREE DATA STRUCTURE
IN A TREE DATA STRUCTURE, EACH ENTRY IS CALLED A NODE.
A NODE MAY HAVE A NUMBER OF CHILDREN AND ONE PARENT.
THE TOP NODE, WHICH HAS NO PARENT, IS CALLED THE ROOT.
THE NODES AT THE BOOTOM WHICH HAVE NO CHILDREN ARE
CALLED LEAVES.
THE NODES IN THE MIDDLE ARE SIMPLE NOLEAF NODES.

View Slide

View Slide

EXAMPLE:
BUG
REPORT
COMMENTS

View Slide

SOLUTIONS
• ADJACENCY LIST
• PATH ENUMERATION
• NESTED SETS
• CLOSURE TABLE

View Slide

ADJACENCY
LIST

View Slide

ADJANCENY LIST
• NAIVE SOLUTION
EVERYONE USES
• EACH ENTRY KNOWS ITS
IMMEDIATE PARENT

View Slide

CREATE TABLE comment (
comment_id BIGINT PRIMARY KEY,
parent_id BIGINT UNSIGNED,
author VARCHAR(255) NOT NULL,
comment TEXT NOT NULL,
FOREIGN KEY (parent_id) REFERENCES comment(comment_id)
);
ADJACENCY LIST

View Slide

class Comment {
String comment
String author
Comment parent
static mapping = {
id column: 'comment_id'
version false
comment type: 'text'
}
static constraints = {
comment nullable: false
author nullable: false
parent nullable: true
}
}
ADJACENCY LIST - GORM

View Slide

ID PARENT_ID AUTHOR COMMENT
1 NULL FRAN WHAT’S THE CAUSE OF THIS BUG?
2 1 OLLIE I THINK IT IS A NULL POINTER
3 2 FRAN NO, I CHECKED FOR THAT
4 1 KUKLA WE NEED TO CHECK FOR VALID INPUT
5 4 OLLIE YES, THAT’S A BUG
6 4 FRAN YES, PLEASE ADD A CHECK
7 6 KUKLA THAT FIXED IT

View Slide

INSERT INTO comment ( parent_id, author,
comment) VALUES (5, ‘FRAN’, ‘I agree!’)
INSERT A NEW NODE

View Slide

INSERT INTO comment ( parent_id, author,
comment) VALUES (5, ‘FRAN’, ‘I agree!’)
INSERT A NEW NODE
Comment saveComment(String comment, String author, Comment ancestorComment) {
def commentEntity = new Comment(comment: comment, author: author, parent:
ancestorComment)
if ( !commentEntity.save() ) {
log.error "Could not save comment ${commentEntity.errors}"
}
commentEntity
}

View Slide

UPDATE comment
SET parent_id=3
WHERE comment_id=6;
MOVE A NODE OR SUBTREE

View Slide

UPDATE comment
SET parent_id=3
WHERE comment_id=6;
MOVE A NODE OR SUBTREE
void moveCommentToParent(Comment comment, Comment newAncestor) {
comment?.parent = newAncestor
if ( !comment?.save() ) {
log.error "Could not change parent of comment ${comment.errors}"
}
comment
}

View Slide

SELECT * FROM COMMENTS C1
LEFT JOIN COMMENTS C2
ON (C2.PARENT_ID = C1.COMMENT_ID);
QUERY INMEDIATE CHILD / PARENT
QUERY A NODE’S CHILDREN
QUERY A NODE’S PARENT
LEFT JOIN COMMENTS C2
ON (C1.PARENT_ID = C2.COMMENT_ID);

View Slide

List childsOfComment(Comment commentEntity) {
Comment.where {
parent == commentEntity
}.list()
}
QUERY INMEDIATE CHILD / PARENT
QUERY A NODE’S CHILDREN
QUERY A NODE’S PARENT
List parentsOfComment(Comment commentEntity) {
if ( commentEntity.parent == null ) {
return []
}
[commentEntity.parent]
}

View Slide

List ancestorsOfComment(Comment commentEntity) {
def comments = []
for ( Comment comment : parentsOfComment(commentEntity)) {
comments << comment
comments += ancestorsOfComment(comment)
}
comments
}
List parentsOfComment(Comment commentEntity) {
if ( commentEntity.parent == null ) {
return []
}
[commentEntity.parent]
}
QUERY ALL ANCESTORS

View Slide

LEFT JOIN COMMENTS C2 ON (C2.PARENT_ID=C1.COMMENT_ID)
…
CAN’T HANDLE DEEP TREES

View Slide

…
CAN’T HANDLE DEEP TREES
It still doesn’t support unlimited depth!

View Slide

RECURSIVE
QUERY

View Slide

WTIH [RECURSIVE] commenttree
(comment_id, parent_id, author, comment, depth)
AS (
SELECT *, 0 AS depth FROM comment
WHERE parent_id IS NULL
UNION_ALL
SELECT c.*, ct.depth+1 AS DEPTH FROM CommentTree ct
JOIN Comments c ON (ct.comment_id = c.parent_id)
)
SELECT * FROM commenttree WHERE bug_id = 1234;
SQL-99 Recursive Syntax
Mysql, SQLite,
Informix, Firebird, etc
PostgresSQL, Oracle 11g, IBM DB2,
Microsoft SQL Server, Apache Derby

View Slide

PATH
ENUMERATION

View Slide

path VARCHAR(1000),
length BIGINT,
comment TEXT NOT NULL
);
PATH ENUMERATION

View Slide

class Comment {
String comment
String author
String path
Integer length
path nullable: false, blank: true
length nullable: false, min: 0
}
static mapping = {
version false
table 'comment'
path sqlType: 'varchar', length: 1000
}
}
PATH ENUMERATION - GORM

View Slide

ID PATH AUTHOR COMMENT
1 1/ FRAN WHAT’S THE CAUSE OF THIS BUG?
2 1/2/ OLLIE I THINK IT IS A NULL POINTER
3 1/2/3/ FRAN NO, I CHECKED FOR THAT
4 1/4/ KUKLA WE NEED TO CHECK FOR VALID INPUT
5 1/4/5/ OLLIE YES, THAT’S A BUG
6 1/4/6/ FRAN YES, PLEASE ADD A CHECK
7 1/4/6/7/ KUKLA THAT FIXED IT

View Slide

ID PATH LENGTH AUTHOR COMMENT
1 1/ 1 FRAN WHAT’S THE CAUSE OF THIS BUG?
2 1/2/ 2 OLLIE I THINK IT IS A NULL POINTER
3 1/2/3/ 3 FRAN NO, I CHECKED FOR THAT
4 1/4/ 2 KUKLA WE NEED TO CHECK FOR VALID INPUT
5 1/4/5/ 3 OLLIE YES, THAT’S A BUG
6 1/4/6/ 3 FRAN YES, PLEASE ADD A CHECK
7 1/4/6/7/ 4 KUKLA THAT FIXED IT
If elements between separators are not
of consistent length, this column allow
you to easily sort a set of rows by
hierarchy

View Slide

SELECT * FROM comment
WHERE ‘1/4/6/7/‘ LIKE path || ‘%’;
QUERY ANCESTORS AND SUBTREES
QUERY ANCESTORS OF comment #7
QUERY DESCENDANTS OF comment #4
SELECT * FROM comment
WHERE PATH LIKE ‘1/4/’ || ‘%’
It matches the patterns
formed from paths of
ancestors ‘1/4/6/%’, ‘1/4/%’, ‘1/%’

View Slide

Comment.createCriteria().list {
or {
paths.each { String subpath ->
and {
like('path', subpath)
eq('length', (subpath.split(SEPARATOR).size() - 1 ))
}
}
}
ne('id', comment.id)
}
QUERY ANCESTORS OF comment #7 => path 1/4/6/7/
paths = ‘1/4/6/%’, ‘1/4/%’, ‘1/%’

View Slide

Comment.where {
id != commentEntity.id && path ==~ “${commentEntity?.path}%"
}.list()
QUERY DESCENDANTS OF comment #4

View Slide

INSERT INTO comment (author, comment)
VALUES (‘Ollie’, ‘Good job!’);
SELECT path FROM Comments WHERE comment_id = 7;
UPDATE comment
SET path = $parent_path || LAST_INSERTED_ID() || ‘/‘
WHERE comment_id = LAST_INSERT_ID();
ADD A NEW CHILD of #7

View Slide

PATH
ENUMERATION
DEMO

View Slide

NESTED SETS

View Slide

nsleft BIGINT,
nsright BIGINT,
);
NESTED SET

View Slide

class Comment {
String comment
Integer nsleft
Integer nsright
String author
static mapping = {
version false
}
nsleft nullable: true
nsright nullable: true
}
}
NESTED SETS - GORM

View Slide

NESTED SETS
Each comment encodes its descendants using
two numbers:
- A Comment’s left number is less than all
numbers used by the comment’s descendants.
- A comment’s right number is greater than all
numbers used by the comment’s descendants.
- A comment’s number are between all
numbers used by the comment’s ancestors.

View Slide


View Slide

ID NSLEFT NSRIGHT AUTHOR COMMENT
1 1 14 FRAN WHAT’S THE CAUSE OF THIS BUG?
2 2 5 OLLIE I THINK IT IS A NULL POINTER
3 3 4 FRAN NO, I CHECKED FOR THAT
4 6 13 KUKLA WE NEED TO CHECK FOR VALID INPUT
5 7 8 OLLIE YES, THAT’S A BUG
6 9 12 FRAN YES, PLEASE ADD A CHECK
7 10 11 KUKLA THAT FIXED IT

View Slide

ID NSLEFT NSRIGHT AUTHOR COMMENT
1 1 14 FRAN WHAT’S THE CAUSE OF THIS BUG?
2 2 5 OLLIE I THINK IT IS A NULL POINTER
3 3 4 FRAN NO, I CHECKED FOR THAT
4 6 13 KUKLA WE NEED TO CHECK FOR VALID INPUT
5 7 8 OLLIE YES, THAT’S A BUG
6 9 12 FRAN YES, PLEASE ADD A CHECK
7 10 11 KUKLA THAT FIXED IT
These are not foreign keys

View Slide

SELECT * FROM comment AS child
JOIN comment AS ancestor ON child.nsleft
BETWEEN ancestor.nsleft AND ancestor.nsright
WHERE child.comment_id = 7
QUERY ANCESTORS OF 7

View Slide

SELECT * FROM comment AS parent
JOIN comment AS descendant ON descendant.nsleft
BETWEEN parent.nsleft AND parent.nsright
WHERE parent.comment_id = 4
QUERY SUTREE UNDER #4

View Slide

UPDATE comment
SET nsleft = CASE WHEN nsleft >= 8 THEN nsleft+2
ELSE nsleft END, nsright = nsright+2
WHERE nsright >= 7;
INSERT INTO comment (nsleft, nsright, author, comment)
VALUES (8,9,’Fran’, ‘I agree!’);
INSERT NEW CHILD of #5
Recalculate left values for all nodes to the
right of the new child. Recalculate right
values for all nodes above to the right.

View Slide

7
8 9
6
10 11
12
13
14
15
16

View Slide

SELECT parent.* FROM comment AS C
JOIN comment AS parent
ON (ns.left BETWEEN parent.nsleft and parent.nsright)
LEFT OUTER JOIN comment AS in_between
ON (c.nsleft BETWEEN in between.nsleft AND in_between.nsright
AND in_between.nsleft BETWEEN in parent.nsleft AND parent.nsright)
WHERE c.comment_id = 6 AND in_between.comment_id IS NULL;
QUERY INMEDIATE PARENT of #6
Parent of #6 is an ancestor who has no
descendant who is also no ancestor of #6

View Slide

CLOUSURE
TABLE

View Slide

);
CREATE TABLE treepath (
ancestor BIGINT NOT NULL,
descendant BIGINT NOT NULL,
PRIMARY KEY (ancestor, descendant),
FOREIGN KEY (ancestor) REFERENCES comment(comment_id),
FOREIGN KEY (descendant) REFERENCES
comment(comment_id)
);
CLOSURE TABLE

View Slide

class CommentTreePath implements Serializable {
CommentGormEntity ancestor
CommentGormEntity descendant
int length
static mapping = {
version false
table 'treepath'
descendant column: 'descendant'
ancestor column: 'ancestor'
id composite: ['ancestor', 'descendant']
}
…
}
class CommentGormEntity {
String comment
String author
static mapping = {
table 'comment'
version false
}
}
String toString() {
"${author}: $comment"
}
}

View Slide

CLOSURE TABLE
- Many to many table
- Stores every path from each
node to each of its
descendants.
- A node even connect to itself

View Slide


View Slide

COMMENT_ID AUTHOR COMMENT
1 FRAN WHAT’S THE CAUSE OF THIS BUG?
2 OLLIE I THINK IT IS A NULL POINTER
3 FRAN NO, I CHECKED FOR THAT
4 KUKLA WE NEED TO CHECK FOR VALID
INPUT
5 OLLIE YES, THAT’S A BUG
6 FRAN YES, PLEASE ADD A CHECK
7 KUKLA THAT FIXED IT
ANCESTOR DESCENDANT
1 1
1 2
1 3
1 4
1 5
1 6
1 7
2 2
2 3
3 3
4 4
4 5
4 6
4 7
5 5
6 6
6 7
7 7
CLOSURE TABLE

View Slide

SELECT c.* FROM comment c JOIN treepath t ON
(c.comment_id = t.descendant) WHERE t.ancestor = 4;
QUERY DESCENDANTS OF #4

View Slide

PATHS
STARTING
FROM #4

View Slide

SELECT C.* FROM Comments c JOIN TreePaths t ON
(c.comment_id = t.ancestor) WHERE t.descendant = 6;
QUERY ANCESTORS OF #6

View Slide

PATHS
TERMINATING
AT#6

View Slide

INSERT INTO Comments VALUES (8, ‘Fran’, ‘I agree!’);
INSERT INTO TreePaths (ancestor, descendant)
SELECT ancestor, 8 FROM TreePaths WHERE descendant 5
UNION ALL SELECT 8, 8;
INSERT NEW CHILD of #5

View Slide

DELETE FROM TreePaths WHERE descendant = 7;
DELETE CHILD #7

View Slide

Delete FROM TreePaths WHERE descendant IN (SELECT
descendant FROM TreePaths WHERE ancestor = 4);
DELETE SUBTREE UNDER #4

View Slide

ANCESTOR DESCENDANT LENGTH
1 1 0
1 2 1
1 3 2
1 4 1
1 5 2
1 6 2
1 7 3
2 2 0
2 3 1
3 3 0
4 4 0
4 5 1
4 6 1
4 7 2
5 5 0
6 6 0
6 7 1
7 7 0
PATH LENGTH
- Add a length column
- MAX (length) is the depth of the tree
- Makes it easier to query immediate
parent or child
SELECT c.*
FROM Comments c
JOIN TreePaths t
ON (c.comment_id = t.descendant)
WHERE t.ancestor = 4 AND t.length = 1;

View Slide

ANCESTOR DESCENDANT LENGTH
1 1 0
1 2 1
1 3 2
1 4 1
1 5 2
1 6 2
2 2 3
2 3 0
3 3 0
4 4 0
4 5 1
4 6 1
4 7 2
5 5 0
6 6 0
6 7 1
7 7 0
PATH LENGTH
- Add a length column
- MAX (length) is the depth of the tree
- Makes it easier to query immediate
parent or child
SELECT c.*
FROM Comments c
JOIN TreePaths t
ON (c.comment_id = t.descendant)
WHERE t.ancestor = 4 AND t.length = 1;

View Slide

CLOSURE
TABLE DEMO

View Slide

DESIGN # TABLES QUERY
CHILD
QUERY
SUBTREE
DELETE
NODE
INSERT
NODE
MOVE
SUBTREE
REF.
INTEG.
ADJACENSY LIST 1 EASY HARD EASY EASY EASY YES
PATH ENUMERATION 1 HARD EASY EASY EASY EASY NO
NESTED SETS 1 HARD EASY HARD HARD HARD NO
CLOUSURE TABLE 2 EASY EASY EASY EASY EASY YES
CHOSING THE RIGHT DESIGN

View Slide

“A hierarchy consists of entries
and relationships.
Model both of these to suit your
work.”

View Slide


View Slide

?

View Slide

OCI
12140 Woodcrest Exec. Dr., Ste. 250
Saint Louis, MO 63141 USA
© 2017, All Rights Reserved.
No part of this publication may be photocopied or reproduced in any form without written permission
from OCI. Nor shall the OCI logo or copyright information be removed from this publication. No part of
this publication may be stored in a retrieval system, transmitted by any means, recorded or otherwise,
without written permission from OCI.
While every precaution has been taken in preparing this material, including research, development and
testing, OCI assumes no responsibility for errors or omissions. No liability is assumed by OCI for any
damages resulting from the use of this information.

View Slide

Mapping a Tree with GORM and Grails

Mapping a Tree with GORM and Grails

Sergio del Amo

More Decks by Sergio del Amo

Other Decks in Programming

Featured

Transcript