When configuring a relational store join, can I do a one-to-many join from the ActivePivot store?

Question

Using Relational Stores, is it possible to do a one-to-many join from the ActivePivot store to a joining store. Suppose my ActivePivot store joins to another store on SOME_ID, but the key for the other store is SOME_ID,SOME_TYPE. Then it is possible to have:

AP_STORE SOME_ID | JOIN_STORE SOME_ID | JOIN_STORE SOME_TYPE
------------------------------------------------------------
      1          |      1             | TYPE1
      1          |      1             | TYPE2

However, when the join is attempted, the following error is raised, because there is not a unique entry in the joining store:

Caused by: com.quartetfs.fwk.QuartetRuntimeException: Impossible to find exactly 1 entry from store with key: Key

I can see why there is a problem, because there single record in the AP store that really needs to become two separate records that join to each of the records in the join store, respectively, but I guess that can't happen unless JOIN_STORE:SOME_TYPE is also a field in the AP store.

Is there a way to make such a one-to-many join from the AP store happen?

Thanks

Edit: To be clear, SOME_TYPE does not exist in the AP store (even under a different name). I have joined on all the common fields, but there are more than one matching entries in the joining store. The matching entries differ on a field that is not common and does not exist in the AP store.

If I try to add a foreign key that does not exist in the AP store (even under a different name), I get:

Caused by: com.quartetfs.fwk.QuartetRuntimeException: com.quartetfs.fwk.AgentException: On join 'AP_STORE=>JOIN_STORE' the store 'AP_STORE' does not contain the foreign key 'FIELD_ONLY_IN_JOIN_STORE' in its fields:

I've just realised that when I merge the entries into the AP store, I can do it by only merging in the extra keys from the joining store rather than all the columns. I can then do a regular relational store join on the combination of the keys, which can potentially save a lot of memory by avoiding duplication. — doc, Sep 27 '12 at 12:59

score 3 · Accepted Answer · answered Sep 21 '12 at 08:53

A relational store join does not duplicate the data. You cannot, using the join of the relational stores, join one entry to multiple ones. You cannot use a multiple producing calculator with the relational stores neither.

Depending of your project architecture and workflow, you can consider adding a logic in the transaction handler used to feed your AP_Store. In this transaction handler, you could retrieve the entries of your Join_Store in order to duplicate the entries of your AP_Store.

You'll first need to change your AP_Store keys by adding a new fields used to differentiate your duplicates.

    AP_STORE SOME_ID | AP_STORE SOME_DUPLICATE_ID |JOIN_STORE SOME_ID | JOIN_STORE SOME_TYPE
    -----------------------------------------------------------------------------------------        
          1          |            1               |         1         |       TYPE1
          1          |            2               |         1         |       TYPE2

For your transaction handler you can inject the StoresUniverse in order to retrieve your Join_Store and then do a search using the SOME_ID value on the Join_Store to retrieve the number of duplicates you'll need to create:

    IRelationalStore joinStore = storeUniverse.get("Join_Store");
    List<IRelationalEntry> joinEntries = joinStore.search("SOME_ID",apStoreObject.get("SOME_ID"));
    for(int i = 0; i < joinEntries.size(); i++) {
       // Clone apStoreObject, add a SOME_DUPLICATE_ID value and add it to the list of objects to add in your AP_Store
    }

score 0 · Answer 2 · answered Sep 21 '12 at 06:55

To join your AP Store to the joiningStore, you need to give a set of fields, common between the 2 stores. There is no constrain like these fields being key fields of each of the store.

Then, if you have a field representing SOME_TYPE in your AP store, just add it as a foreign key.

<property name="joins">
    <list>
        <bean class="com.quartetfs.tech.store.description.impl.JoinDescription">
             <property name="targetStoreName" value="JoiningStore" /> 
             <property name="foreignKeys" value="SOME_TYPE" /> 
        </bean>
    </list>
</property>

If the field has different names in the joined store and the joining store, you can use a map to describe the relationship between the joined store foreign key and the associated field in the joining field:

<property name="joins">
    <list>
        <bean class="com.quartetfs.tech.store.description.impl.JoinDescription">
             <property name="targetStoreName" value="JoiningStore" /> 
             <property name="foreignKeyMap" >
                 <map>
                      <entry key="AP_SOME_TYPE" value="SOME_TYPE" />
                 </map>
             </property>
        </bean>
    </list>
</property>

Unfortunately, I don't think this answers my question. Please see the edit to my question above. — doc, Sep 21 '12 at 08:29

When configuring a relational store join, can I do a one-to-many join from the ActivePivot store?

2 Answers2

Linked