Grails one-to-many using natural ID and referenced column name

Question

I have the following entities:

class ProductVariant {
    int id
    long sku

    static hasMany = [prices: ProductPrice]
}

class ProductPrice {
    int id
    int priceBandId
    long sku
    BigDecimal price
}

I am trying to achieve a one-to-many relationship between ProductVariant and ProductPrice where the join is on the sku as opposed to the id column.

I have seen an example of how this works in Hibernate using a @NaturalId annotation on the sku column and a referencedColumnName property in the @JoinColumn annotation.

Is is possible to achieve the same thing using Grails/GORM?

The key thing here is that each store has its own set of variants with their own IDs, but the prices are set at a SKU level and would apply across all stores stocking the same SKU.

Answer 1

It turns out that, whilst this is fine at the database level, it's not really something you do. Grails won't let you have a hasMany association on just one part of the primary key.

Instead I am just retrieving all prices by adding a getter to my ProductVariant domain object as follows:

List<ProductPrice> getPrices() {
    return ProductPrice.findAll { it.sku == this.sku }
}

and then I can just use productVariant.prices when needed.