Querying a grails tree object

Question

I have a Section domain that looks like this. I build the navigation from it and order stories under sections or sub sections eg: news > crime > the great train robbery

class Section {
    String name;
    String path
    Section parent;
    Boolean active = true;
    int level = 0;
    Boolean isRoot = false;
    static hasMany = [children:Section]
    static mappedBy = [children:'parent']
    static mapping = {
        parent :'all-delete-orphan' 
        parent lazy:true
    }
}

The Story domain that looks like this

    class Story 
    {
        String title;
        String story;
        List link;
        List substory;
        List picture;
        Section publishUnder
        boolean parent = false;
        boolean approved = false;

        Date dateCreated;
        Date lastUpdated;

        static belongsTo = [author:Author]

        static hasMany=
        [
           link:String,
           substory:Story,
           picture:Picture
        ]
   }

We have 7 root sections:

Home, News, Sport, Travel, Tech, Culture and Comment and with the exception of Home they all have child sections

eg: News will have a level below it containing UK, Europe, USA then USA could have another level of child sections for containing Politics and Crime Sections. Stories as mentioned above are stored under Sections. So a story "Obama Impeached" would have as its published under value
News > USA > Politics.

I have to write a query that will get n number of stories which are stored under one of the children of the top level sections. So if I pass in the 'News' Section as parameter (there might not be any stories stored directly under "News" but there may be under News > Uk> Crime) I want to return stories that are stored under any child section of "News"

How do I do this with gorm? Should I use HQL? I'm still a noob to this so any help would be appreciated.

felipenasc felipenasc · Accepted Answer · 2013-05-24T15:46:53

You can have a simple solution like this:

First, to speed things up, I would have a Sections Tree in a memory cache. When you start up the application, you can create this Tree and store it in an applicatin context: In grails-app/conf/BootStrap.grrovy , you can do this inside init closure

def init = { servletContext ->
    servletContext.sectionsTree = something
}

To create this "something", you can traverse the sections children recursively and construct a list or map that contains all children´s id of each section... something like:

rootSections = [[section:root1, childrenIds:[23,45,67,89,5,43,45,6,7,87], children:[section:child1, childrenIds:[23,45,67]]], [section:root2, childrenIds:[32,43,54,65,76], children:[]]]

Where childrenIds contains all IDs from the sections children, and children of children.

So now, you can execute a simple HQL query to get the Section´s Stories:

Story.executeQuery("select s from Story s where s.publishUnder.id in (?)", [childrenIds]);

Thats just one solution....se what you think about it.

Cheers

Querying a grails tree object

1 Answers