CODEWITHSUNDEEP
grailsgroovygrails-orm
buchan
buchan

Reputation: 11

GORM sort by a has_many

this is in the context of Grails and GORM

If domain A has many of domain B, and domain B has a field called name which is a string, what is the neatest way of producing a list of A which has been sorted based on its "best" value of B. So for example if an instance of A has B's with names of "Andrew" and "Zed" then it should come before an A with B's of name "Jim" and "Fred".

This is what I have, looking for something a bit more Grails-y:

def apps = B.executeQuery("select distinct c from A as c left join fetch c.bs as b order by b.name",[max:pmax, offset:poffset])

Upvotes: 1

Views: 1847

Answers (1)

anthonylawson
anthonylawson

Reputation: 781

I don't usually execute SQL queries directly, I just use GORM to handle them.

If you can set up your domain class as a bidirectional association (unidirectional association is not supported), then you could do something like this:

class classA {

    static constraints = {
    }

    static mapping = {
        bList sort :'name', order:'asc'
    }

    static hasMany = [bList: classB]
}

And now class B:

class ClassB {

    static constraints = {
    }

    static belongsTo = [classAInstance: classA]

    String name
}

I added this to the BootStrap.groovy file to add some instances:

class BootStrap {

    def init = { servletContext ->
        def a = new ClassA()
        def b1 = new ClassB(name: 'Andrew')
        def b2 = new ClassB(name: 'Lisa')
        def b3 = new ClassB(name: 'Walter')
        def b4 = new ClassB(name: 'Brandon')
        def b5 = new ClassB(name: 'Cathy')

        a.addToBList(b1)
        a.addToBList(b2)
        a.addToBList(b3)
        a.addToBList(b4)
        a.addToBList(b5)
        a.save()
    }

    def destroy = {
    }
}

Then this is the controller I used to test it out:

class TestController {

    def index() {
        def aInstance = ClassA.get(1)
        def lst = aInstance.bList

        lst.each { println it.name }
    }
}

You should be able to go to http://localhost:8080/test/test/index and then look at the where ever stdout is printed and then you shoue see:

Andrew
Brandon
Cathy
Lisa
Walter

There may be some better ways of doing certain parts of this, but this is what I could think of off the top of my head...

Upvotes: 1

Related Questions