Fetching vs Inserting
There is a little note about the way AirData requests work in Connection Builder.
When fetching Objects that contain references to other objects, the `__ids` in the objects are expanded to the full objects. The problem comes when you then try to insert the object or update the list.
The same object cannot be inserted
In order to do this you need you get all the ids out of the objects that are in the array of objects and replace that. Hopefuly this will be fixed some day, but this is what you need now.
Say you have an object:
Collector
- Name
- Cards (array of card)
Cards:
- Player Name
- Position
In order to insert a new card into a collectors cards you need to do the following:
- Create the new card
- Retrieve the collector
- Update the collector object by getting the ids of the old cards and adding the new card
- Update the collector row with an AirData Request.
The super interesting part of this is the AirData for updating the card collection, it looks something like this:
MERGE_OBJECTS(
collector,
{
"cards": FROM
i
IN
FLAT(
[ FROM c IN collector.cards SELECT c.id, [ newCard.__id ] ]
)
WHERE
NOT(
i = NULL
)
SELECT
i
}
)To clarify what this is doing. It’s taking the collector object merging it with another object that only contains the `cards` list. The cards list is created by taking the list of existing cards and extracting the id, adding the new id. This combining is done with the fuction called `FLAT()` then I’m filtering out the null items (if the cards array was empty when it started).
The resulting object can then be put back into the table to update the existing record. The `__id` of the collector will still be same because of the `MERGE_OBJECTS()` call at the top.
