Hi,
I've got a problem with a join-query which I can not convert to a set of
objects the way I'd like.
The problem is that the base table is empty.
I'm querying the following 2 tables:
SetInstance
-----------
setId PK
setInstanceId PK
CollectionSetInstance
-----------
setId PK
setInstanceId PK
collectionId PK
number...
I want to display a list of all SetInstances to the user, including the
number of times he has the SetInstance (field number...) in his Collection.
The table CollectionSetInstance is initially empty.
When a user adds a SetInstance to his Collection a row in
CollectionSetInstance is added.
When the user wants to add another SetInstance to his Collection, the
field 'number' is increased.
Things I've tried:
1. My first attempt was to query SetInstance en outer join
CollectionSetInstance.
This retrieves the correct result (all setInstances), but Cayenne
forces me to add a relation from SetInstance to CollectionSetInstance?
This is not what I want, because functionally there is not
relationship between SetInstance and CollectionSetInstance.
2. I've also tried to use a SQLTemplate query and convert that to the
desired object.
This has not worked because Cayenne gives me an exception.
See this code:
SQLTemplate query = new SQLTemplate(CollectionSetInstance.class,
"SELECT " +
"SI.setId, " +
"SI.setInstanceId, " +
"CSI.collectionId, " +
"CSI.number " +
"FROM SetInstance SI " +
"LEFT OUTER JOIN CollectionSetInstance CSI ON " +
"CSI.setId = SCI.setId " +
"AND CSI.setInstanceId = SCI.setInstanceId " );
EntityResult result = new EntityResult(CollectionSetCardInstance.class);
result.addDbField(CollectionSetCardInstance.SET_ID_PK_COLUMN, "setId");
result.addDbField(CollectionSetCardInstance.SET_INSTANCE_ID_PK_COLUMN,
"setInstanceId");
result.addDbField(CollectionSetCardInstance.COLLECTION_ID_PK_COLUMN,
"collectionId");
result.addObjectField(CollectionSetCardInstance.NUMBER_PROPERTY, "number");
List list = context.performQuery(query);
Can anyone help me out on this one? If you need any more information,
let me know.
Greets,
Elt
