Can you join and group by tables with SQL expressions?

[Matt-e8a4]

I have been enjoying honeycode for a project, but ran into my first blocker that I cannot work around.

I have two tables, listed below. i want to do something SQL-like to create an aggregated list of ingredients for a shopping list (IE, combine garlic from all recipes into one line item).

Is this possible in any way in honey code?

Select
meal_event_details.recipe_name,
recipe_ingredients.ingedient,
recipe_ingredients.unit,
SUM(recipe_ingredients.quantity)

FROM meal_event_details
LEFT JOIN recipe_ingredients ON meal_event_details.recipe_name = recipe_ingredients.recipe_name

GROUP BY
meal_event_details.recipe_name,
recipe_ingredients.ingedient,
recipe_ingredients.unit,

Table 1: recipe_ingredients:

recipe_name	ingredient	quantity	unit
Tomatoes with sumac shallots and pine nuts	shallot	70.00	g
Tomatoes with sumac shallots and pine nuts	sumac	1.50	pinch
Tomatoes with sumac shallots and pine nuts	white wine vinegar	2.00	tsp
Tomatoes with sumac shallots and pine nuts	tomato	700.00	g
Tomatoes with sumac shallots and pine nuts	olive oil	2.00	tbsp
Tomatoes with sumac shallots and pine nuts	basil	15.00	g
Tomatoes with sumac shallots and pine nuts	pine nut	25.00	g
spicy moroccan carrot salad	carrot	2.00	lb
spicy moroccan carrot salad	olive oil	0.33	cup
spicy moroccan carrot salad	onion	1.00	ea
spicy moroccan carrot salad	sugar	1.00	tsp
spicy moroccan carrot salad	garlic	3.00	clove
spicy moroccan carrot salad	green chili	2.00	ea
spicy moroccan carrot salad	green onion	1.00	ea
spicy moroccan carrot salad	ground clove	0.13	tsp
spicy moroccan carrot salad	ground ginger	0.25	tsp
spicy moroccan carrot salad	ground coriander	0.50	tsp
spicy moroccan carrot salad	ground cinnamon	0.75	tsp
spicy moroccan carrot salad	paprika - sweet	1.00	tsp
spicy moroccan carrot salad	ground cumin	1.00	tsp
spicy moroccan carrot salad	white wine vinegar	1.00	tbsp
spicy moroccan carrot salad	preserved lemon	1.00	tbsp
spicy moroccan carrot salad	cilantro	2.50	cup
spicy moroccan carrot salad	greek yogurt	0.50	cup
spiced shepherd’s pie with lima bean crust	olive oil	6.00	tbsp
spiced shepherd’s pie with lima bean crust	garlic	3.00	clove
spiced shepherd’s pie with lima bean crust	shallot	3.00	ea
spiced shepherd’s pie with lima bean crust	ground beef	600.00	g
spiced shepherd’s pie with lima bean crust	cumin seed	2.00	tsp
spiced shepherd’s pie with lima bean crust	allspice	1.00	tbsp
spiced shepherd’s pie with lima bean crust	lemon	2.00	ea
spiced shepherd’s pie with lima bean crust	tomato paste	3.00	tbsp
spiced shepherd’s pie with lima bean crust	harissa	3.00	tbsp
spiced shepherd’s pie with lima bean crust	apricots	100.00	g
spiced shepherd’s pie with lima bean crust	chicken stock	280.00	ml
spiced shepherd’s pie with lima bean crust	white wine	220.00	ml
spiced shepherd’s pie with lima bean crust	green olive	80.00	g
spiced shepherd’s pie with lima bean crust	lima bean	670.00	g
spiced shepherd’s pie with lima bean crust	tahini	70.00	g
smoky marinated feta	garlic	10.00	clove
smoky marinated feta	lemon	1.00	ea
smoky marinated feta	bay leaf	4.00	ea
smoky marinated feta	chipotle chili flake	2.50	tsp
smoky marinated feta	olive oil	250.00	ml
smoky marinated feta	sea salt	1.00	tsp
smoky marinated feta	paprika	1.25	tsp
smoky marinated feta	feta	360.00	g
Shatta	tomato	90.00	g
Shatta	fresno chili	180.00	g
Shatta	kosher salt	0.50	tsp
Shatta	apple cider vinegar	3.00	tbsp
Shatta	olive oil	8.00	tbsp
honey roasted carrots with tahini yogurt	honey	3.00	tbsp
honey roasted carrots with tahini yogurt	olive oil	2.00	tbsp
honey roasted carrots with tahini yogurt	coriander seed	1.00	tbsp
honey roasted carrots with tahini yogurt	cumin seed	1.50	tsp
honey roasted carrots with tahini yogurt	thyme sprig	3.00	ea
honey roasted carrots with tahini yogurt	carrot	3.00	lb
honey roasted carrots with tahini yogurt	cilantro	1.50	tsp
honey roasted carrots with tahini yogurt	tahini	3.00	tbsp
honey roasted carrots with tahini yogurt	greek yogurt	130.00	g
honey roasted carrots with tahini yogurt	lemon juice	2.00	tbsp
honey roasted carrots with tahini yogurt	garlic	1.00	clove

Table 2: meal_event_details

event_date	meal_type	meal_component	recipe_name
12/30/20	dinner	main	spiced shepherd’s pie with lima bean crust
12/30/20	dinner	side	honey roasted carrots with tahini yogurt

[Alyssa]

Hi @Matt-e8a4,

Welcome to the community, and thanks for your post!

For this, I would suggest using the IN operator to check if an ingredient is already in the shopping list. If an ingredient already is, you can just add to the quantity of that ingredient.

The concept in Honeycode is using Filter formulas similar to below, and running a particular action for those returned rows:

=FILTER(IngredientsTable, "Ingredients[ingredient] IN ShoppingList[Ingredient]")

To show you how to do this in Honeycode, I created an app that achieves what you're asking for

Here's how I set it up:

I have an EventMeals table with the meals that will be for a given event:

An Ingredients table, where I added two different recipes that had at least one of the same ingredients (garlic, in this case):

A Recipes table, which has the recipe name and a column that filters all of the ingredients:

And a ShoppingList table to aggregate all the ingredients for the added recipes:

In Builder I created a screen, where a user can select a recipe that they wish to add to a shopping list. The Recipe variable is a picklist where the user can select an item from the recipes table:

Below it I have two lists, one that will display the added recipes and the other that will display the ingredient shopping list (both filtered by the event date):

EventMeals list:

Shopping List:

I also created a variable that just has a filter of the shopping list for this event. You will see in the following steps how it can simplify formulas in your automation:

The Add button is where you'll add the automation that checks if the ingredients in an added recipe are already in the shopping list and take actions accordingly.

For the automation of the Add button, I have three actions:

The first action writes the event date and the recipe to the EventMeals table:

The second action checks each of the ingredients in the added recipe to see if it is already in the shopping list. This is done with a filter formula in the Run Options, that uses the IN function:

If the ingredient is in the shopping list, have the action overwrite the quantity value with the new value (added quantity from added recipe):

And finally, the third will only add the ingredients if they are not already in the shopping list. You will want to set up the Run Options to check that the ingredients are NOT in the shopping list yet:

And if the ingredients are not in the shopping list yet, the action will add a new row for each ingredient:

Now you have an app that will allow you to add multiple recipes to an event, and the shopping list will aggregate the total quantity of each ingredient needed (one line for each ingredient):

This is also reflected in the table:

Let me know if this example helps and if you have any other questions around this.

[Matt-e8a4]

This is amazing - I was able to completely build this out in my larger/less simple app. I have 2 follow-up questions:

1. I don't fulling understand the purpose of the "Recipes" table you made--- what purpose does it have and where is it used?

2. If I wanted to add a Delete button to a single row of EventMeals (like delete 1 of 2 meals) --- how would I decrement the shopping list? I spent a few days struggling to make this work, but could not get it to work.

thanks again for this detailed example

[Alyssa]

Hi @Matt-e8a4,

Great! I'm happy you've been able to build your app

For your questions:

1 ) I created a Recipes table to help organize and relate the data. Since the Ingredients table has multiple ingredients for the same recipe, the recipe column in the Ingredients table is a rowlink to the Recipes table to relate the data.

2 ) I'm happy to show you how to create a delete button For your example, I created one which removes a recipe from an event and the ingredients from the shopping list:

In the EventMeals list, I added a Remove button. It has three different actions on it, which I'll expand on below:

The first action is an Overwrite that will subtract the ingredients of the recipe from the Shopping List. It filters for the ingredients of the recipe being removed from the ingredients table:

The second action deletes ingredients from the shopping list, if the quantity is zero. This is done by filtering the event's shopping list for any ingredients that have 0 in the quantity and deleting the context row:

Lastly, the third action deletes the recipe from the EventMeals list. This is done by simply specifying to delete the context row, which is the equivalent of using THISROW():

Now your app allows you to remove recipes from your event, which will also remove the ingredients from the shopping list

I'll highlight that the order of these actions is important, as you need overwrite rows before deleting context rows.

Hope this helps!

[Matt-e8a4]

This worked perfectly - adding [quantity])-[quantity] after the findrow was the key to this for me.

[Alyssa]

Awesome I'm glad it's working for you, @Matt-e8a4!

Let us know if you have any other building questions that come up, we're always happy to help