CODEWITHSUNDEEP
grailsmany-to-manygrails-ormjoin
rks
rks

Reputation: 451

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

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?

Upvotes: 11

Views: 5820

Answers (3)

Maicon Mauricio
Maicon Mauricio

Reputation: 3011

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

Upvotes: 0

hakuna1811
hakuna1811

Reputation: 534

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)

Upvotes: 13

Jeff Beck
Jeff Beck

Reputation: 3938

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.

Upvotes: 7

Related Questions