0

I have multiple tables with foreign keys, and I'm facing challenges when it comes to consolidating multiple values in my current SQL select with joins. The issue arises when a foreign ID is present multiple times, causing the query to return redundant rows with the same data (except for one column), this is for a restaurant App menu I'm developing for my brother.

enter image description here

Below is the current SQL code I am using, which I have turned into a view:

select D.dish_id, TD.translation as dish_name, D.dish_price, TC.translation as cat_name, TI.translation as ing_name
from Dish D
join Dish_Category DC on D.dish_id = DC.dish_id
join Category C on DC.category_id = C.category_id
join Translations_Categories TC on C.category_id = TC.category_id and TC.language = "en"
join Translations_Dish TD on D.dish_id = TD.dish_id and TD.language = "en"
join Dish_Ingredient DI on D.dish_id = DI.dish_id
join Ingredient I on DI.ingredient_id = I.ingredient_id
join Translations_Ingredient TI on I.ingredient_id = TI.ingredient_id and TI.language = "en"
ORDER by D.dish_id

Currently, the query returns all the elements with redundant informations, the only information change between same dish_id is the ing_name column:

dish_id dish_name       dish_price  cat_name    ing_name
    1   Tomato Sauce    5.00        Pizzas      Tomato
    2   White Sauce     5.00        Pizzas      Mozzarella
    3   Margherita      5.00        Pizzas      Tomato
    3   Margherita      5.00        Pizzas      Mozzarella
    4   Napoli          6.00        Pizzas      Tomato
    4   Napoli          6.00        Pizzas      Mozzarella
    4   Napoli          6.00        Pizzas      Capers
    4   Napoli          6.00        Pizzas      Anchovies

Here I have all the info I need (not true, each ingredient, as you can see from the graph, has 2 value I need, but 1 thing per time).

However, I would like to find a way to consolidate the data so that there's only one row per ID, and the ingredient names are packed into arrays within the corresponding column. An ideal result would look like this:

dish_id dish_name       dish_price  cat_name    ing_name
    1   Tomato Sauce    5.00        Pizzas      Tomato
    2   White Sauce     5.00        Pizzas      Mozzarella
    3   Margherita      5.00        Pizzas      [Tomato, Mozzarella]
    4   Napoli          6.00        Pizzas      [Tomato, Mozzarella, Capers, Anchovies]

Going forward, I would need to complicate things a bit, have 2 ulterior values for each ingredient! Maybe something like a json data?

dish_id dish_name       dish_price  cat_name    ing_name
    1   Tomato Sauce    5.00        Pizzas      Tomato
    2   White Sauce     5.00        Pizzas      Mozzarella
    3   Margherita      5.00        Pizzas      {"Tomato": {"allergenes": false, "frozen" : false}, "Mozzarella": {"allergenes": true, "frozen" : false} }
    4   Napoli          6.00        Pizzas      {"Tomato": {"allergenes": false, "frozen": false}, "Mozzarella": {"allergenes": true, "frozen": false}, "Capers": {"allergenes": true, "frozen": false}, "Anchovies": {"allergenes": true, "frozen": false}}

I would greatly appreciate any guidance or suggestions on optimizing the SQL query and efficiently packing the data for frontend display. Additionally, if there are better approaches for displaying data on the frontend, instead of using a single query of the view, please let me know. Thank you!

Raikoug
  • 347
  • 3
  • 16
  • You can do this on the mysql side (see duplicate questions), however, it may be better if you did the jsonify in your application a leave mysql to be a relational database, not a nosql one! – Shadow Jul 23 '23 at 23:04
  • By this, do you mean I should do more queries in the backend, correlated then with code and then serving this to frontend? Isn't SQL far superior in correlating data? – Raikoug Jul 23 '23 at 23:09
  • Sql is great at combining data from multiple tables, but less effective in formatting the data into hierarchical structures. – Shadow Jul 24 '23 at 00:55

0 Answers0