Details

    • Type: Sub-task Sub-task
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 1.0
    • Fix Version/s: 1.1-beta1
    • Component/s: Persistence
    • Labels:
      None

      Description

      Implementation Recommendations...

      1) New syntax for the hasMany definition that allows multiple M2M relationships. Addition of a 'join' attribute that identifies the name of the relationship in the other DO. It would be ideal if 'join' was optional for DOs with only a single M2M relationship.
      2) Join table is named based on the singularized concatonation of the two relationship names. The relationship name from the owning DO should be first.
      3) FKs should be table + "_" + id.

      There is a challange in that many databases limit table and column names to a certan number of characters (I believe in the case of Oracle it's 30).

      Example:

      class Book {
          static hasMany [authors:[object:Person, join:books],
          publishers:[object:Person, join:publishedBooks]]
          static belongsTo = Person
      }
      
      class Person {
          static hasMany [books:[object:Book, join:authors],
          publishedBooks:[object:Book, join:publishers]]
      }
      

      This would generate 4 tables. 2 base DO tables (BOOK, PERSON) and 2 join tables (AUTHOR_BOOK, PUBLISHER_PUBLISHED_BOOK). Adding . Let's add some data so that we have the following, one book (book.id= 1) and two authors (person.id = 55 and person.id = 66) and a publisher (person.id = 77).

      BOOK

      ID NAME
      1 Some Book

      PERSON

      ID NAME
      55 Sue
      66 Jim
      77 Matt

      AUTHOR_BOOK

      BOOK_ID PERSON_ID
      1 55
      1 66

      PUBLISHER_PUBLISHED_BOOK

      BOOK_ID PERSON_ID
      1 77

      The following SQL would then work...

      select book.name, person.name as author
      from book, person, author_book
      where book.id = author_book.book_id
      and person.id = author_book.person_id

      ...resulting in the following...

      NAME AUTHOR
      Some Book Sue
      Some Book Jim

        Activity

        Tyler Williams created issue -
        Peter Ledbrook made changes -
        Field Original Value New Value
        Fix Version/s 1.1 [ 13674 ]
        Hide
        Tyler Williams added a comment -

        In re-reading this issue, it didn't seem entirely clear that in addition to enhancing the hasMany functionality, I am hoping that the default behavior is changed.

        To anyone with a database design background, the current behavior is counterintuitive. As we see on the mailing list at least once a week someone posts their confusion with naming of the join table's foreign keys (and I agree it is confusing, the explanation in JIRA-828 notwithstanding). If Grails is trying to adhere to conventions this is a place where it currently misses the mark.

        Using the following two classes and the BOOK/PERSON data from the issue description above, you would end up with a join table called PERSON_BOOK where the ID from the BOOK table is stored in a column called AUTHORS_ID and the ID from the PERSON table is stored in a column called BOOKS_ID. So in addition to the non-standard pluralization of the column name, the IDs in the join table appear reversed. Sure you could look at the constraints, but it could/should be clearer just looking at the column names.

        class Person

        { static hasMany = [books: Book] }

        class Book

        { static hasMany = [authors:Person] static belongsTo = Person }

        PERSON_BOOK (current counterintuitive behavior)

        AUTHORS_ID BOOKS_ID
        1 55
        1 66

        So my suggestion for the default behavior is when there is a single M2M, you would not need to specify the new object & join parameters, however you would get a join table like the following.

        PERSON_BOOK

        PERSON_ID BOOK_ID
        55 1
        66 1
        Show
        Tyler Williams added a comment - In re-reading this issue, it didn't seem entirely clear that in addition to enhancing the hasMany functionality, I am hoping that the default behavior is changed. To anyone with a database design background, the current behavior is counterintuitive. As we see on the mailing list at least once a week someone posts their confusion with naming of the join table's foreign keys (and I agree it is confusing, the explanation in JIRA-828 notwithstanding). If Grails is trying to adhere to conventions this is a place where it currently misses the mark. Using the following two classes and the BOOK/PERSON data from the issue description above, you would end up with a join table called PERSON_BOOK where the ID from the BOOK table is stored in a column called AUTHORS_ID and the ID from the PERSON table is stored in a column called BOOKS_ID. So in addition to the non-standard pluralization of the column name, the IDs in the join table appear reversed. Sure you could look at the constraints, but it could/should be clearer just looking at the column names. class Person { static hasMany = [books: Book] } class Book { static hasMany = [authors:Person] static belongsTo = Person } PERSON_BOOK (current counterintuitive behavior) AUTHORS_ID BOOKS_ID 1 55 1 66 So my suggestion for the default behavior is when there is a single M2M, you would not need to specify the new object & join parameters, however you would get a join table like the following. PERSON_BOOK PERSON_ID BOOK_ID 55 1 66 1
        Graeme Rocher made changes -
        Parent GRAILS-3396 [ 73760 ]
        Issue Type Improvement [ 4 ] Sub-task [ 7 ]
        Graeme Rocher made changes -
        Description Implementation Recommendations...

        1) New syntax for the hasMany definition that allows multiple M2M relationships. Addition of a 'join' attribute that identifies the name of the relationship in the other DO. It would be ideal if 'join' was optional for DOs with only a single M2M relationship.
        2) Join table is named based on the singularized concatonation of the two relationship names. The relationship name from the owning DO should be first.
        3) FKs should be table + "_" + id.

        There is a challange in that many databases limit table and column names to a certan number of characters (I believe in the case of Oracle it's 30).

        Example:

        class Book {
            static hasMany [authors:[object:Person, join:books],
            publishers:[object:Person, join:publishedBooks]]
            static belongsTo = Person
        }

        class Person {
            static hasMany [books:[object:Book, join:authors],
            publishedBooks:[object:Book, join:publishers]]
        }

        This would generate 4 tables. 2 base DO tables (BOOK, PERSON) and 2 join tables (AUTHOR_BOOK, PUBLISHER_PUBLISHED_BOOK). Adding . Let's add some data so that we have the following, one book (book.id= 1) and two authors (person.id = 55 and person.id = 66) and a publisher (person.id = 77).

        BOOK
        ||ID||NAME||
        |1|Some Book|

        PERSON
        ||ID||NAME||
        |55|Sue|
        |66|Jim|
        |77|Matt|

        AUTHOR_BOOK
        ||BOOK_ID||PERSON_ID||
        |1|55|
        |1|66|

        PUBLISHER_PUBLISHED_BOOK
        ||BOOK_ID||PERSON_ID||
        |1|77|

        The following SQL would then work...

        select book.name, person.name as author
        from book, person, author_book
        where book.id = author_book.book_id
        and person.id = author_book.person_id

        ...resulting in the following...
        ||NAME||AUTHOR||
        |Some Book|Sue|
        |Some Book|Jim|

        Implementation Recommendations...

        1) New syntax for the hasMany definition that allows multiple M2M relationships. Addition of a 'join' attribute that identifies the name of the relationship in the other DO. It would be ideal if 'join' was optional for DOs with only a single M2M relationship.
        2) Join table is named based on the singularized concatonation of the two relationship names. The relationship name from the owning DO should be first.
        3) FKs should be table + "_" + id.

        There is a challange in that many databases limit table and column names to a certan number of characters (I believe in the case of Oracle it's 30).

        Example:

        {code}
        class Book {
            static hasMany [authors:[object:Person, join:books],
            publishers:[object:Person, join:publishedBooks]]
            static belongsTo = Person
        }

        class Person {
            static hasMany [books:[object:Book, join:authors],
            publishedBooks:[object:Book, join:publishers]]
        }
        {code}

        This would generate 4 tables. 2 base DO tables (BOOK, PERSON) and 2 join tables (AUTHOR_BOOK, PUBLISHER_PUBLISHED_BOOK). Adding . Let's add some data so that we have the following, one book (book.id= 1) and two authors (person.id = 55 and person.id = 66) and a publisher (person.id = 77).

        BOOK
        ||ID||NAME||
        |1|Some Book|

        PERSON
        ||ID||NAME||
        |55|Sue|
        |66|Jim|
        |77|Matt|

        AUTHOR_BOOK
        ||BOOK_ID||PERSON_ID||
        |1|55|
        |1|66|

        PUBLISHER_PUBLISHED_BOOK
        ||BOOK_ID||PERSON_ID||
        |1|77|

        The following SQL would then work...

        select book.name, person.name as author
        from book, person, author_book
        where book.id = author_book.book_id
        and person.id = author_book.person_id

        ...resulting in the following...
        ||NAME||AUTHOR||
        |Some Book|Sue|
        |Some Book|Jim|

        Graeme Rocher made changes -
        Status Open [ 1 ] In Progress [ 3 ]
        Hide
        Graeme Rocher added a comment -

        A relationship like:

        class Book {
            String title
            static belongsTo = Author
            static hasMany = [authors:Author]
        }
        class Author {
             String name
             static hasMany = [books:Book]
        }
        

        Now maps onto a table called "author_books" like:

        author_id book_id
        11

        You can also completely alter the mapping with the ORM DSL:

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

        And multiple many-to-many relationships are supported with the mappedBy syntax that existed already:

        class Book {
            String title
            static belongsTo = Person
        
            static mappedBy = [publishers:'publishedBooks',
                                           authors:'books']
            static hasMany = [authors:Person,
                                         publishers:Person]
        }
        class Person {
            String name
            static mappedBy = [books:'authors',
                                           publishedBooks:'publishers']
            static hasMany = [books:Book,
                                         publishedBooks:Book]
        }
        
        Show
        Graeme Rocher added a comment - A relationship like: class Book { String title static belongsTo = Author static hasMany = [authors:Author] } class Author { String name static hasMany = [books:Book] } Now maps onto a table called "author_books" like: author_id book_id 11 You can also completely alter the mapping with the ORM DSL: 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'] } } And multiple many-to-many relationships are supported with the mappedBy syntax that existed already: class Book { String title static belongsTo = Person static mappedBy = [publishers:'publishedBooks', authors:'books'] static hasMany = [authors:Person, publishers:Person] } class Person { String name static mappedBy = [books:'authors', publishedBooks:'publishers'] static hasMany = [books:Book, publishedBooks:Book] }
        Graeme Rocher made changes -
        Status In Progress [ 3 ] Closed [ 6 ]
        Resolution Fixed [ 1 ]
        Hide
        Dierk Koenig added a comment -

        why is in author_books the word book*s* plural but author singular? How is the logic which class is singular and which is plural?

        Show
        Dierk Koenig added a comment - why is in author_books the word book*s* plural but author singular? How is the logic which class is singular and which is plural?
        Hide
        Graeme Rocher added a comment -

        The name is formulated from the owning class (Author) and the association name (books). They both cannot be singular because then if you had two many-to-many associations they would share the same join table (author_book). Previously the way we got around this was to name the columns after the associations, but that caused the confusion hence the change.

        Show
        Graeme Rocher added a comment - The name is formulated from the owning class (Author) and the association name (books). They both cannot be singular because then if you had two many-to-many associations they would share the same join table (author_book). Previously the way we got around this was to name the columns after the associations, but that caused the confusion hence the change.
        Hide
        Dierk Koenig added a comment -

        Perfect. This explanation should go into the online docs (and into the book, I'm afraid )

        Show
        Dierk Koenig added a comment - Perfect. This explanation should go into the online docs (and into the book, I'm afraid )
        Hide
        Graeme Rocher added a comment -

        Its already in the book

        Show
        Graeme Rocher added a comment - Its already in the book
        Contegix Support made changes -
        Project Import Thu Mar 24 21:22:24 CDT 2011 [ 1301019744151 ]
        Burt Beckwith made changes -
        Workflow jira [ 30541 ] Grails [ 43473 ]
        Burt Beckwith made changes -
        Workflow Grails [ 43473 ] Copy of Grails [ 50927 ]
        Burt Beckwith made changes -
        Workflow Copy of Grails [ 50927 ] Grails [ 58337 ]
        Burt Beckwith made changes -
        Workflow Grails [ 58337 ] Grails2 [ 65915 ]
        Burt Beckwith made changes -
        Workflow Grails2 [ 65915 ] jira [ 77969 ]
        Burt Beckwith made changes -
        Workflow jira [ 77969 ] Grails2 [ 86250 ]
        Peter Ledbrook made changes -
        Workflow Grails2 [ 86250 ] jira [ 87429 ]
        Peter Ledbrook made changes -
        Workflow jira [ 87429 ] Grails2 [ 95723 ]

          People

          • Assignee:
            Graeme Rocher
            Reporter:
            Tyler Williams
          • Votes:
            16 Vote for this issue
            Watchers:
            16 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development