Per an answer to a previous question (answer here: http://stackoverflow.com/questions/425294/sql-database-views-in-grails#427691), 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.