Grails Domain Class without ID field or with partially NULL composite field
Per an answer to a previous question (answer here: SQL/Database Views in Grails), I have tried to use a domain class to represent a view in my database. This works wonderfully in most cases, however:
I have a view with no single unique key. Let's say the underlying tables look like this:
A:
id,varX
1,"Blah"
2,"Foo"
3,"Bar"
B:
id,A.id,C.id
1,2,1
2,2,2
3,3,1
C:
id,varY
1,"Boom"
2,"Fizzle"
My view looks like this:
A.id,varX,B.id,C.id,varY
1,"Blah",NULL,NULL,NULL
2,"Foo",1,1,"Boom"
2,"Foo",2,2,"Fizzle"
3,"Bar",3,1,"Boom"
That is exactly how it ought to look for our purposes. However, as you can see, the best unique composite id we could construct for the view is ['A.id','C.id'], as this uniquely identifies each element, but Grails fails because it cannot seem to deal with part of a composite ID being NULL (actually, list() returns a list of 4 objects, the first is a null pointer, the rest are actual domain instances of the view).
Note that we could also use A.id and B.id but it suffers from the same problem.
Note also that we WANT to display elements from table A at least once (with null values for any fields not found in tables B/C), possibly many times if there are multiple corresponding entries in table B.
So, my question is 2 parts:
1: Is it possible to define a grails domain class without any ID field at all? We do not need a permanent handle to any of the views entries, we just need to list the data in that view.
2: If not, is it possible to define a grails domain class with a composite ID field, part of which may be NULL, but which will uniquely identify a row anyway even if part of the composite ID is NULL?
I know we can use straight Groovy SQL to query the view directly without an associated domain class (we are doing this now actually), but ideally we'd like to represent the view with a domain class. Further, all arguments aside, these two questions can be applied much more generically than to just our particular problem.
Answer
We had that problem before.
In my experience, Composite IDs are just not well supported in Grails or maybe in Hibernate.
We always find a way to have unique ID for all our domain classes.
For real tables, we just add an auto-increment field, even though if we weren't using grails we would have used a Composite key.
For database views, we usually have an ID already in one of the joined tables that ends up being unique in that context, but if we don't, there are ways to hack/simulate this. For example, in SQL, just concatenate the keys you would use in your composite key as a single field and use this as the key.