How to use one of the association column as primary key of the table in Grails?

Question

How do we create domain class for the following scenario.

CREATE TABLE station
(
  store_id integer NOT NULL,
  pos_id integer NOT NULL,
  CONSTRAINT pos_locations_pk PRIMARY KEY (store_id, pos_id)
)

CREATE TABLE header
(
  id serial NOT NULL,
  store_id integer NOT NULL,
  pos_id integer NOT NULL,
  ....
  CONSTRAINT sales_transactions_pk PRIMARY KEY (id, store_id),
  CONSTRAINT sales_transactions_pos_fk FOREIGN KEY (store_id, pos_id)
      REFERENCES station (store_id, pos_id) 
)

CREATE TABLE line_item
(
  id serial NOT NULL,
  sale_id bigint NOT NULL,
  store_id integer NOT NULL,
  .....
  CONSTRAINT transaction_lines_pk PRIMARY KEY (id, store_id),
  CONSTRAINT transaction_lines_sale_fk FOREIGN KEY (sale_id, store_id)
      REFERENCES header (id, store_id)
)

I started as following for Station domain

class Station {
    int storeId
    int posId

    static mapping = {
        table "station"
        id composite: ['storeId', 'posId']
        storeId column: 'store_id'
        posId column: 'pos_id'
    }
   }

and for header table

class Header {
int id
Station station

static mapping = {
    cache false
    version false
    table "header"

    columns {
        station {
             column name: 'store_id'
             column name: 'pos_id'
        }
    }
    id composite: ['id', 'station.storeId'] // Where I have to use store_id alone from association column to be included as a part of composite key to header table
}
}

and for line_item table

class LineItem {
int id
Header header

static mapping = {
    table "line_item"
    columns {
        header {
            column name: 'sale_id'
            column name: 'store_id'
        } 
    }
    id composite: ['id', 'header.station.storeId'] // something like this where I have to include store_id along from header table which instead from station table should be included as a part of composite key
}
}

Answer 1

The problem is there's a mismatch between the table relationships dictated by the database schema and the associations in the domain classes.

For example, there's a many-to-one association from Header to Station, yet the database specifies:

1. an explicit many-to-one relationship from Header to Station, through the foreign key
2. and an implicit many-to-one from Header to Store (a table not shown but I'm assuming exists), through the composite primary key

The same scenario exists for the LineItem.

Grails is actually unintentionally revealing something important about the database schema. There's no sugar-coating it: it's flawed. If the intent were to be explained in plain English as...

1. A store can have many stations.
2. A station can have many headers.
3. A header can have many line items.

...then the database schema should reflect:

1. a one-to-many from store to station
2. a one-to-many from station to header
3. a one-to-many from header to line item

With such as schema your Grails domain models could be mapped seamlessly. To answer your question briefly, you can't do what you're attempting because Grails/Hibernate associations are based on properties not properties of properties.

Immutable database schema

If the database schema cannot be fixed, you'd have to forgo the associations in your domain classes. You can make the domain classes little more than Row Data Gateways. But in doing so you'd limit the capabilities of your dynamic, where, criteria, and HQL queries; no table/domain joins.

If you only need read-only access and you're allowed to create database views, here's a wild idea that may not even work: create the views you need and then create domain classes for the views.