cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Eltaeb <elt...@gmail.com>
Subject Problem with joining two tables, base one empty.
Date Thu, 03 Dec 2009 22:04:02 GMT
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

Mime
View raw message