GORM many-to-many mapping and jointable with an additional field

Question

I am working with legacy database and have a many-to-many association with a join-table that I have resolved to a large extent as the mappings are working fine. But there is an additional column and in case of Book, Author model lets say that the nm_author_books contain a field called 'royalty'. Question is how do I access this field from any direction?

class Book {
    String title
    static belongsTo = Author
    static hasMany = [authors: Author]
    static mapping = { authors joinTable: [name: "mm_author_books", key: 'mm_book_id' ] } 
}
class Author {
    String name
    static hasMany = [books: Book]
    static mapping = { books joinTable: [name: "mm_author_books", key: 'mm_author_id'] } 
}

If the nm_author_book table has [nm_book_id, nm_author_id, royalty] what is the way to access the royalty?

Answer 1

The basic idea is change from 1 many-to-many to 2 many-to-one: a Book has many BookAuthorDetail and a Author has many BookAuthorDetail

class Book {
    String title

    static hasMany = [details: BookAuthorDetail]
}
class Author {
    String name
    static hasMany = [details: BookAuthorDetail]
}

class BookAuthorDetail {
    String royalty
    static belongsTo = [book: Book, author: Author]
}

to access royalty by BookAuthorDetail, you can do: BookAuthorDetail.findAllByBookAndAuthor(bookInstance, authorInstance)