Grails/GORM : Legacy DB tree structure domains (recursively)

[Sebastian YEPES FERNANDEZ]

Hello,

 

I am trying to create the corresponding domain classes for a legacy database that uses the typical tree structure (hierarchy) to store some group names.

I would like to do the standard (ReadOnly) operations on this tree structure:

• Find all the child's (recursively) of a given group name.
• From a given child (group name) retrieve all its parent groups (recursively).

Can some one please guide me with some examples, on how I could achieve this using the Grails ORM (GORM).

 

Below is a simplified version of the DB schema and GORM domain classes that i have created for the moment:

 

 

Table schema and data:

GROUPS

 group_id

 parent_id

 group_name

 

Data (GROUPS):

 1, null, 'Root group'

 2, 1, 'Sub group1'

 3, 1, 'Sub group2'

 4, 3, 'Child'

 

GRPS_IN_GRPS

 group_id

 member_group_id

 

Data (GRPS_IN_GRPS):

 1,2

 1,3

 4,3

 

Final result:

-Root group

 -Sub group1

 -Sub group2

  -Child

 

 

 GORM Domains:

class Groups {

  String groupId

  String parentId

  String groupName

 

  static hasMany = [grps : GroupRels]

  static mappedBy = [grps:'srcGrp']

 

  static mapping = {

    version false

    table 'GROUPS'

    id generator:'assigned', column:'GROUP_ID', name:'groupId'

  }

 

  String toString() { return "${groupId}" }

}

 

class GroupRels implements Serializable {

  String groupId

  String memberGrpId

 

  static belongsTo = [ srcGrp : Groups ]

 

  static mapping = {

    version false

    table 'GRPS_IN_GRPS'

    id composite:['groupId', 'memberGrpId'], generator:'assigned'

    srcGrp column:'GROUP_ID'

  }

  String toString() { return "${groupId}" }

}

 

 

Thanks in advance for any help.

 

 

[Kamil Mikolajczyk]

Hello

You have a lot of redundant data which may cause problem - you have information about parent of every node, and also information about children of every parent, so you have to remember to always set two relations and be cafeful not to desync them. On the other hand, it might improve performance if used correctly.

In your domain classes, you don't have to store any IDs explicitly, this is ORM, so you can nest objects in objects and GORM/hibernate will translate it to id's by itself.

class Group {

  String name

  Group parent

  static mapping = {

    table name: 'GROUPS'

    parent column: 'parent_id'

    id generator:'assigned', column:'group_id',name:'id'

  }

}

class GroupRelation {

  Group group

  Group member

  static mapping = {

    table name: 'GRPS_IN_GRPS'

    group column: 'group_id'
    member column: 'member_group_id'

    id composite: ['group', 'member']

  }

}

 

I wrote it without IDE, so there may be some errors, but in general I'd do it like this.

Kind regards

Kamil Mikolajczyk