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

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:

Solution

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
    }