How do I join two data collections?
I currently have the following formula below:
SELECT<order_item>(data.order_item_collection, IS_IN_ARRAY_BY_KEY(data.menu_item_collection, “objectId”, order_item.menu_item_objectId) && order_item.order_objectId == params.order_parameter)
I want to access the data from the data.menu_item_collection as well but I have run out of ideas how to do this. I was able to do something like this:
SELECT<order_item>(data.order_item_collection && data.menu_item_collection, IS_IN_ARRAY_BY_KEY(data.menu_item_collection, “objectId”, order_item.menu_item_objectId) && order_item.order_objectId == params.order_parameter)
However, the repeat with function doesn’t like the formula and is confused on what to list. Please help.
I simply want to do the same as the dummy SQL query below:
SELECT * FROM data.order_item_collection order_item
INNER JOIN data.menu_item_collection menu_item ON order_item.menu_item_objectId = menu_item.objectId
CONCAT formula lets you join two lists into one.
Thanks so much for getting back to me. It’s nice to hear from a AppGyver Team Member. Your solution does work in that it provides me with both lists. However, its not quite what I want. All CONCAT does is gives me the two lists. I want to join two lists together based on an item from data collection A equals an item in data collection B.
I used your CONAT below in the formula:
CONCAT(SELECT(data.order_item_collection, IS_IN_ARRAY_BY_KEY(data.menu_item_collection, “objectId”, item.menu_item_objectId) && item.order_objectId == params.order_parameter), SELECT(data.menu_item_collection, IS_IN_ARRAY_BY_KEY(data.order_item_collection, “menu_item_objectId”, item.objectId)))
How would I further filter on an item in the first SELECT to an item in the second SELECT. Or is that not possible?
You can wrap as many SELECTs around SELECTs as you want to filter more.
Hi, just dropped the tip as I was having some trouble understanding the use case – in the end, what kind of collection do you want to have? A list of
order_item where the
objectId === params.order_parameter and where
menu_item_objectId can be found in
That would be:
SELECT(data.order_item_collection, item.objectId === params.order_parameter && IS_IN_ARRAY_BY_KEY(data.menu_item_collection, “objectId”, item.menu_item_objectId))
which is actually what you had at the beginning (except for the formula lacking one closing brace), did you also want to include the
menu_item objects in this?
Thanks for the tip @JOHN_WORSHAM
Yes that is what I am after @Mari. Having the menu_item object included in it too.
As their own objects (will result in an array of mismatched objects) or as a field in the
Are you able to provide both examples and I can see what works best for my use case? Thanks heaps for your help so far.
Hi, if you first save the selected items in a page variable as
selected_order_items the first approach would be
CONCAT(pageVars.selected_order_items, SELECT(data.menu_item_collection, IS_IN_ARRAY_BY_KEY(pageVars.selected_order_items, "menu_item_objectId", item.objectId)))
and the second where you append the
menu_item to each corresponding
order_item as a field using
MAP<order_item>(pageVars.selected_order_items, SET_KEY("menu_item", FIND(data.menu_item_collection, item.objectId === order_item.menu_item_objectId))
Thank you @Mari
I’ll try this out and get back to you.
Any further update in this? Was it successful, @Tim_McIntosh ?