CODEWITHSUNDEEP
grailsgrails-orm
Jim Sosa
Jim Sosa

Reputation: 638

Gorm not doing join

I'm trying to build a hierarchy of a class I've called group. The domain is fairly simple:

class SubGroup implements Serializable {
    Group child
    Group parent

    static mapping = {
        id composite: ['child', 'parent']
    }
}

class Group implements Serializable {           
    int groupId
    String key
    String title

    static mapping = {
        id name: 'groupId'
    }
}

Basically I'd like to build up a map of group parents and their associated children. So I'm looping through each record (if there's a cleaner way to query for a Map I'm happy to hear it) and creating the map entry for it. 

Map hierarchy = [:]
SubGroup.list().each { relation ->
    if (!hierarchy[relation.parent]) {
        hierarchy[relation.parent] = new HashSet()
    }

    hierarchy[relation.parent] << relation.child
}

I would think hibernate would use some simple query that does this something like this:

select * from sub_group s, group c, group p 
where s.child_id = c.group_id and s.parent_id = p.group_id

But it is not doing the join. It is doing a query of the subgroup followed by n queries of the group table (N+1 select problem). Ugh. I heard in 2.0 there's a problem with the hibernate query cache so I disabled it. I've tried adding lazy: false, and fetch: join to my SubGroup domain class for both the parent and the child columns with no luck. I've tried adding (fetch: [child: 'eager']) as a parameter to the list method. It will not do the join. Am I missing something? To be honest, it shouldn't really even need to do a join as I am only accessing the groupId foreign key property although later on I will need both the key and title properties.

I could, of course, make the child and parent properties ints and just do my own querying when I need the rest of the data or I could use HQL and some other method for limiting this to a single query, but it seems like GORM should be doing this for me. Thanks for your help.
Regards,

Jim

Upvotes: 0

Views: 267

Answers (1)

Jim Sosa
Jim Sosa

Reputation: 638

It seems the only way I could get this to work without hibernate making a select call for each child and parent was to instead do a HQL query rather than using the list method as shown below:

Map hierarchy = [:]
def subGroups = SubGroup.executeQuery("SELECT s.parent, s.child FROM SubGroup s")
subGroups.each { relation ->
    if (!hierarchy[relation[0]]) {
        hierarchy[relation[0]] = new HashSet()
    }

    hierarchy[relation[0]] << relation[1]
}

Thus I'm really only using the SubGroup domain class to define the relationships. I would think I should be able to do this via some kind of mapping but I couldn't figure out how.

Upvotes: 0

Related Questions