Grails: sort order on one-to-many relationship not working

Question

I've got a simple bi-directional one-to-many mapping, as follows, with a default sort order specified on the owning side. However, the sort order doesn't seem to be getting applied? I'm using Grails v2.0.1 (I've now replicated this example with v1.3.7).

package playground

class User {

    String name

    static hasMany = [ posts : Post ]

    static mapping = {
        posts sort:'position'
    }
}

and

package playground

class Post {

    int position = 1
    String message

    static belongsTo = [ user : User ]
}

this is the integration test code I'm using to exercise it ...

    def User user = new User(name:'bob')
    user.addToPosts(new Post(position:2, message:'two'))
    user.addToPosts(new Post(position:3, message:'three'))
    user.addToPosts(new Post(position:1, message:'one'))

    assertTrue user.validate()
    assertFalse user.hasErrors()
    assertNotNull user.save()

    for (post in user.posts) {
        log.debug "Post message> ${post.message}"
    }

Please put me out of my misery, it's presumably something obvious but I can't see it! Thanks.

Answer 1

use this code:

package playground

    class User {

        String name
        static hasMany = [ posts : Post ]

        static mapping = {
            posts sort:'position' order:'desc'//order:'asc'
        }
    }

Answer 2

Turns out this is a bit of odd edge-case behaviour, that's really a result of the way the test is written. Basically, everything is happening in the scope of a single Hibernate session/txn (see conversations above). So the test is fetching back the object graph it just created (with the out of order Set), rather than fetching data from the database.

If you force a separate transaction then you get the behaviour you are looking for and the 'order by' works as expected. E.g.

User.withNewSession{ session ->
   def User foundUser = User.get(user.id);

   for (post in foundUser.posts) {
      println "Post message> ${post.message}"
   }
}

Code courtesy of ndtreviv.

Answer 3

Owens answer got me out of the confusion i was in. i was trying to the the ordering defined on a relationship between users (1) and posts (many), but when i wrote the initial tests they were failing as using User.get (u.id) was in the same session - and so was just reading out of the cache and they came back in the order i'd written tem not newest first as i'd expected.

I then rewrote the test across two sessions and low and behold in the second session this time returned the posts in desc order.

So you just have to be careful. if you are in the same original session that creates the posts then you have to use User.get (u.id).posts.sort().

All these little gotchas with not understanding properly how the session cache and underlying DB work in the scope of the same session/transaction. makes your brain ache sometimes.

whilst we are noting things - this errored in integration test in 3.2.5, but i spotted a thread by Jeff and the fix that had gone. So i upgraded to grails 3.2.6 last night and this test now works

void "test query"() {
    given:"a user and where query for users, and posts "
    User u

    when: "create a post for user "
    User.withNewSession { session ->
        u = new User(username: 'will')
        u.save(flush: true, failOnError: true)
        Post p1 = new Post(comment: [food: "bought coffee and cake "])
        Post p2 = new Post(comment: [dinner: "bought wine and dinner"])
        Post p3 = new Post(comment: [view: "spectacular view of lake and sunset"])
        u.addToPosts(p1)
        u.addToPosts(p2)
        u.addToPosts(p3)
        u.save(flush: true)
        if (u.hasErrors())
            println "error saving posts on user u : ${u.errors}"


        def postList = User.get(u.id).posts
        postList.each { println "query via user.list using same session > $it.dateCreated : $it.comment" }

        Post.findAll().each { println "query via Post using same session > $it.dateCreated : $it.comment" }
    }
    //because still in same session it just returns the order from the 1st level cache - so force a
    //new session and let the DB do the sort
    def lookupPosts
    User.withNewSession{ session ->
        User uNew  = User.get(1)
        assert uNew
        lookupPosts = uNew.posts

        lookupPosts.each {println "query via user in new session > $it.dateCreated : $it.comment" }
    }



    then: " check post was added"
    !u.hasErrors ()
    lookupPosts.size() == 3
    lookupPosts[1].comment.dinner == "bought wine and dinner"

}

Answer 4

If you use a list instead of a Set (the default) the framework will maintain the order for you.

List posts = new ArrayList()
static hasMany = [ posts : Post ]