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?

Blondy

Answer 1

You could make a domain object that models that join table so instead of changing the mapping for Book and Author you have them point to the AuthorBookRoyalty domain.

Class AuthorBookRoyalty {
  Author author
  Book book
  Long royalty
}

class Book {
  String title
  static belongsTo =Author
  Static hasMany[authors: AuthorBookRoyalty]
}

Do similar for Author and you can now deal with the royalties. You may need to adjust the mapping on the join table to make it map to your current database.

Hardman

Hope it helps its not exactly the same but it should at least give you most of the same functionality. – Hardman

Answer 2

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)

Leukemia

Please don't add "thank you" as an answer. Once you have sufficient reputation, you will be able to vote up questions and answers that you found helpful. – Undervest

I did update my answer, so can you un-down-vote for me? I am trying to fix my answer problem – Leukemia

Answer 3

Another option is to access these additional fields directly from the mapped entity of the join table:

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'] } 
}

class AuthorBooks {
    Author author
    Book book
    Long royalty

    static mapping = {
        table name: "mm_author_books"
        author column: "mm_author_id"
        book column: "mm_book_id"
    } 
}

You can access royalty with the following code, for instance:

AuthorBooks.findAllByBookAndAuthor(bookInstance, authorInstance)*.royalty

Congo