Issues with Grails with cascade deletion with multiple belongTo listed in classes

Question

I am trying to develop a restful server with Grails but I am having trouble getting the domain classes correct. I am using Grails 2.4.2.

I will have a Story class and a User class. The Story class will have a single "author" which is of type User. It will also have many "editors" and many "viewers" which will be a collection of Users. I have tried many different ways of describing this in Groovy for GORM to translate to mySQL but different problems arose for each one. This following scenario seems to be the closest I've gotten to a good solution, but I've hit a snag. If I delete a User instance that is also in one of the Story instance's editors collection, I get

Cannot delete or update a parent row: a foreign key constraint fails (`test`.`editor`, CONSTRAINT `FK_jhrniu8kj891sx2i73mgtgc0v` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)); nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`editor`, CONSTRAINT `FK_jhrniu8kj891sx2i73mgtgc0v` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`))

How can I get this to work? I thought the belongsTo in Editor.groovy pointing to User would cascade deletion. I am open to a better way of setting up the domain classes as I'm relatively new to Grails and GORM.

Here's the code:

Story.groovy

class Story {
    String title
    User author
    static belongsTo = [User]
    static hasMany = [viewers: Viewer, editors: Editor]
    static constraints = {
        viewers nullable: true
        editors nullable: true
    }
}

User.groovy

class User {
    String username
    static hasMany = [stories : Story]
    static constraints = {
        username nullable:false
    }
}

Editor.groovy

class Editor {
    User user
    static belongsTo = [story: Story, user: User]
    static constraints = {
    }
}

Test code:

void "Testing removal of a user and editors"() {
    given: "New Users, New Story"
    def joe = new User(username:"joe").save(failOnError: true)
    def jane = new User(username:"jane").save(failOnError: true)
    def bill = new User(username:"bill").save(failOnError: true)
    def story1 = new Story(author: joe, title:"Story1 Title").save(failOnError: true)   
    when: "let's add some editors and then delete a user"
    story1.addToEditors(new Editor(user: jane))
    story1.addToEditors(new Editor(user: bill))
    story1.save(failOnError: true, flush: true)
    assert story1.editors.size() == 2
    println("before bill deleted " + story1.dump())
    bill.delete(flush: true)  //<-ERROR occurs here
    then: "Should only be one editor now"
    println("after bill deleted:" + story1.dump())
    story1.errors.errorCount == 0
    story1.editors.size() == 1
}

I've tried adding a beforeDelete closure in User, but I get the same error.

    def beforeDelete = {

            Editor.findAllByUser(it)*.delete()

        }

Answer 1

The belongsTo in your Story class is BelongsTo instead of belongsTo. It needs to be belongsTo.

Answer 2

I don't know if this is the best way or not. A friend mentioned to organize the domain classes a bit differently and to implement some getters and setters. It reminded me of Roles used in Spring Security Core so I looked at the UserRole class from that module and noticed they do not use belongsTo or hasMany in the relationships between User, Role, and UserRole. In fact, I did a test where I created a UserRole and then tried to delete the User without removing UserRole and I got the same a foreign key constraint fails error. In this direction I created the following code that passes the integration tests and I don't get the contraint fails error. It is a little bit more work. Maybe when I understand GORM better, I can simplify this. (I borrowed the removeAll methods from Spring Security Core)

class Story {
    def roleService

    String title

    static constraints = {
   }

    def getEditors() {
        roleService.findAllByStory(this, Role.EDITOR)
    }

    def addToEditors(User u) {
        roleService.addToRole(u, this, Role.EDITOR)
    }

    def removeEditor(User u){
        roleService.removeRole(u, this, Role.EDITOR)
    }

    def beforeDelete() {
        def story = this
        Story.withNewSession {
            Editor.removeAll(story, true)
            Viewer.removeAll(story, true)
        }

    }
    //TODO add getters and setters for Viewers and Authors
}

User.groovy

class User {

    String username

    static constraints = {
        username nullable:false
    }
}

Role.groovy

class Role {
    User user
    Story story
    static constraints = {
    }
    static void removeAll(Story s, boolean flush = false) {
            if (s == null) return

            Role.where {
                story == Story.load(s.id)
            }.deleteAll()

            if (flush) { Role.withSession { it.flush() } }
    }

//TODO implement removeAll(User u, boolean flush=false)

    static final int EDITOR = 1
    static final int VIEWER = 2
    static final int AUTHOR = 3
}

Editor.groovy

class Editor extends Role{

    static constraints = {
    }
}

Viewer.groovy

class Viewer extends Role{

    static constraints = {
    }
}

RoleService.groovy

@Transactional
class RoleService {

    public findAllByStory(Story s, int role){
        switch (role ){
            case Role.EDITOR:
                return Editor.findAllByStory(s).collect {e -> e.user}
                break;
            case Role.VIEWER:
                return Viewer.findAllByStory(s).collect {v-> v.user}
                break;
        }

    }

    public addToRole(User u, Story s, int role){
        switch (role ){
            case Role.EDITOR:
                 new Editor(user: u, story: s).save()
                break;
            case Role.VIEWER:
                new Viewer(user: u, story: s).save()
                break;
        }
    }

    public removeRole(User u, Story s, int role){
        if(u == null || s == null) {return false}
        switch (role ){
            case Role.EDITOR:
                 Editor.where {
                    user.id == u.id &&
                    story.id == s.id
                }.deleteAll()
                break;
            case Role.VIEWER:
                Viewer.where {
                    user.id == u.id &&
                    story.id == s.id
                }.deleteAll()
                break;
        }
    }