Join two data collections

Hello,
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

Hi, the CONCAT formula lets you join two lists into one.

Hi @Mari,
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 data.menu_item_collection ?

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 order_item objects?

Hey @Mari
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. :slight_smile:

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 SET_KEY:

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. :slight_smile:

Any further update in this? Was it successful, @Tim_McIntosh ?

Hi @Kaushik_Bhattacharya
Apologies on the really delayed reply. I haven’t logged back into my account for quite some time. Both suggestions by Mari worked but depends what you want to do with the data.
I now do a lot of my query filtering on Xano API to stop appgyver from having to do the heavy lifting on the front end these days.