Let's say I have this kind of MySQL database structure :
Menu :
id name
3 Tartiflette
4 Lasagne
Ingredient :
id name price
15 Carrot 2.00
39 Garlic 3.00
40 Reblochon 5.00
55 Onion 1.00
Menu_ingredient :
id ingredient_id menu_id quantity
4 15 4 2
5 55 4 1
6 39 4 1
7 40 3 2
8 55 3 3
Resa_menu :
id resa_id menu_id people
1 1 4 3
2 1 3 2
Resa :
id
1
How could I get all the ingredients from one reservation, using one query parameter with the items/ endpoint ?
I tried the query using items/resa_menu?fields=*.* (and some other combinations, but out of luck).
I also set up an SQL View (virtual table) getting all info needed with a query, but this kind of SQL doesn't seem to be fetched by Directus.
SELECT
ingredient.name as ing_name,
(resa_menu.people * ingredient.price) as price,
(resa_menu.people * menu_ingredient.quantity) as quantity
FROM resa_menu
INNER JOIN menu ON menu.id = resa_menu.menu_id
INNER JOIN resa ON resa.id = resa_menu.resa_id
INNER JOIN menu_ingredient ON menu_ingredient.menu_id = menu.id
INNER JOIN ingredient ON ingredient.id = menu_ingredient.ingredient_id
GROUP BY ingredient.id;
Expected output :
ing_name price quantity
Carrot 6.00 6
Garlic 9.00 3
Reblochon 10.00 4
Onion 3.00 3
The specific goal here is to get a summary of all ingredients needed for the reservation (combining all menus, multiplying quantity by number of people). But my question is more generic on : how can you do multiple JOINs like in this query with Directus API ?
Many thanks for the help!
I think what you mean is you're requesting the endpoint /items/Resa_menu and doing *.* is returning Menu_ingredient related data, however you would like a list of ingredients returned too? for this, would you not just change your fields query to *.*.* to get three levels of relations?
Or is it that the ?fields=*.* simply isn't working at all?