[BUG] - Merging ingredient doesnt work if referenced by a shopping list

[salco]

First Check

• This is not a feature request.
• I added a very descriptive title to this issue (title field is above this).
• I used the GitHub search to find a similar issue and didn't find it.
• I searched the Mealie documentation, with the integrated search.
• I already read the docs and didn't find an answer.
• This issue can be replicated on the demo site (https://demo.mealie.io/).

What is the issue you are experiencing?

If you want to merge an ingredient that is already used inside a shopping list nothing is show to the user and it doesnt work.
In reality we get a http response: 500 when we look at the network log.
Also if we look at the database (for me it's postgres) you see that the error is:

[21915] ERROR:  update or delete on table "ingredient_foods" violates foreign key constraint "shopping_list_items_food_id_fkey" on table "shopping_list_items"

To confirm:
I've deleted the shopping list in question and retry the merge and all went well.

Steps to Reproduce

1. create tomayto and tomato
2. create 2 recipes, one using tomayto the other using tomato
3. create a new shopping list: I give you the freedom to find a funny name 😉
4. use the 2 recipes to add all ingredient to the shopping list
5. then go to the the option or with the api call direcly api/foods/merge and merge tomayto into tomato

Voila you should get the response 500 in the browser logs.

Please provide relevant logs

2024-05-19 21:46:29.354 EDT [21915] ERROR:  update or delete on table "ingredient_foods" violates foreign key constraint "shopping_list_items_food_id_fkey" on table "shopping_list_items"
2024-05-19 21:46:29.354 EDT [21915] DETAIL:  Key (id)=(※※※※※※※※-※※※※-※※※※-※※※※-※※※※※※※※※※※※) is still referenced from table "shopping_list_items".
2024-05-19 21:46:29.354 EDT [21915] STATEMENT:  DELETE FROM ingredient_foods WHERE ingredient_foods.id = '※※※※※※※※-※※※※-※※※※-※※※※-※※※※※※※※※※※※'::UUID

Mealie Version

1.6.0

Deployment

Docker (Linux)

Additional Deployment Details

Details
Version: v1.6.0
Build: 418a8ec
Mode de l'application: Production
Mode démo: Non démo
Port de l'API: 9000
Documentation de l'API: Activé
Type de base de données: postgres
Version du Scraper de recette: 14.56.0

Checks
Site sécurisé: Oui
URL de base côté serveur: Oui
Prêt pour LDAP: Non
Prêt pour OIDC: Non
Courriel configuré: Non

[ChrisLane]

Just came to post the same bug. I found this when trying to delete Food Data for an item that was in a shopping list.

I think the UI should offer to delete the referencing shopping list entry and perhaps specify which shopping list is referencing the ingredient.

Mealie 1.7.0 for me.

[michael-genson]

Deleting foods should just work, it shouldn't get caught up by foreign key issues.

Ideally when deleting foods, existing shopping list items are either deleted, or their food is converted to a note before deletion. Ingredients should probably do this too as along as we're adding custom food deletion logic.

[salco]

But in my case it's not deleting but updating since I'm merging one keyword into another.
If the food is a key can we not just change it for the new one, one way or another?