Problem with joining two tables, base one empty.

From: Eltaeb (eltae..mail.com)
Date: Thu Dec 03 2009 - 17:04:02 EST

  • Next message: Dave Lamy: "Referencing ID fields in EJBQL"

    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

    This archive was generated by hypermail 2.0.0 : Thu Dec 03 2009 - 17:04:40 EST