How do I create a composite primary key using GORM?

Question

I have three domain classes: Beer, Review, and Reviewer.

I want the Review table to create a many to many relationship between Beer and Reviewer, so I want the primary key of Review to be a composite of the id fields from Beer and Reviewer. I'm following this Grails documentation.

http://grails.org/doc/latest/guide/5.%20Object%20Relational%20Mapping%20(GORM).html#5.5.2.5%20Composite%20Primary%20Keys

Here are my domain classes.

class Beer {
    String name
    String type
    Brewery breweryId

    static hasMany = [ reviews : Review ]

    static constraints = {
    }
}

class Reviewer {
    String screenName

    static hasMany = [ reviews : Review ]

    static constraints = {
    }
}

class Review implements Serializable {
    int score
    Beer beer
    Reviewer reviewer


    static constraints = {
    }

    static mapping = {
        id composite:['beer', 'reviewer']
    }
}

I was getting compilation errors, but another answer here on stackoverflow said I needed to add implements Serializable. That took care of the error, but when I look in the database, I'm still not getting a composite primary key.

Here is what I'm seeing when I look at the table definition. I'm using Postgres.

       Table "public.review"
   Column    |  Type   | Modifiers 
-------------+---------+-----------
 id          | bigint  | not null
 version     | bigint  | not null
 beer_id     | bigint  | not null
 reviewer_id | bigint  | not null
 score       | integer | not null
Indexes:
    "review_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "fkc84ef75823f39326" FOREIGN KEY (beer_id) REFERENCES beer(id)
    "fkc84ef7587c483106" FOREIGN KEY (reviewer_id) REFERENCES reviewer(id)

I'd be happy with just a composite index with a unique constraint, but I can't figure out how to do that, either. I've been able to make a non-unique composite index, but this has two problems. One, it's non-unique. Two, the columns are specified in alphabetical order in the index (beer_id, reviewer_id). I'd like to specify the order of the columns in the index.

Answer 1

I have implemented a similar situation, with some different conditions:

1. There's no hasMany relationship.
2. Query to the join class is done by HQL
3. Using a more detailed mapping

When implementing like this, the mysql database is ok. (beer_id,reviewer_id) is the primary key.

class Review implements Serializable {

    Beer beer
    Reviewer reviewer

    static Review get(long beerId, long reviewerId) {
        find 'from Review where beer.id=:beerId and reviewer.id=:reviewerId',
            [beerId: beerId, reviewerId: reviewerId]
    }

    static boolean remove(Beer beer, Reviewer reviewer, boolean flush = false) {
        Review instance = Review.findByBeerAndReviewer(beer, reviewer)
        instance ? instance.delete(flush: flush) : false
    }
    ...

    static mapping = {
        table "REVIEW"
        id composite: ['beer', 'reviewer']
        beer(column: "beer_ID")
        reviewer(column: "reviewer_ID")
        version false
    }
}

I don't know what exactly causes your problem, but hope this gives you some hint about where the problem can be.